-2

I'm an intermediate Excel user, and am currently starting my exciting journey through Power Pivot and Power BI. Compelled mostly by data getting ever more complex and from many sources!

I'm working on building an HR People Productivity report based on Power Pivot. The raw data added to my data model looks something like this:

Name ..... Total Hrs .... Chargeable Hrs .... Training Hrs .... Prof Dev Hrs .... Mgmt Hrs
Mary ....... 82 ............... 82 ............................ 0.......................0 ......................... 0
Jane ....... 82 ............... 70 ............................ 5 ...................... 5 ........................ 2
Joseph ... 41 ............... 30 ............................ 11 .................... 0 ......................... 0

I've built my metrics setting measures such as:
Training %:=IFERROR([Sum of Training Hrs]/[Sum of Total Hrs],0)

So in my resulting pivot table I get:
Name .... Chg % .... Training % .... Prof Dev % .... Mgmt% ....Comment
Mary....... 100% ...... 0% ................. 0% .................. 0% ...........
Jane ...... 85.3% ..... 6% ................. 6% .................. 2.4% ........
Joseph .. 73% ........ 26.8% ............ 0% .................. 0% ...........

In the column comment -which will display the measure I'm trying to figure!- I need to insert a text measure to point out deviations like "Main source of non chargeable time is [Measure name] at [value in "0.0%" format]", in the case of Joseph for example would point out that Training % is at 26.8%

I've been looking around and could not find a method to compare measures vs other measures (in my actual file I have many more non-chargeable %'s to compare with). All I've found is ways to get mac values within columns.

So what I'm looking for is a way to pick the highest non chargeable % for each person, and then the text measure I need will quote the %'s name and its value.

Really appreciate your wise answers! Been trying several approaches for a while now. Thanks a lot!

Luis Emilio Wierna
  • 1
  • 1

1 Answers1

0

If you are using a version of Power Pivot which supports variables, there is a possible way to do this.

The idea being that you first define a variable for each of your measures that you will compare.

You then define a variable that holds the value of the max value across measures, note that unlike excel the Max function only takes two inputs, thus you have to have a bunch of nested MAX.

Finally you use a switch statement to test your variable of your measure vs the highest value and return the label.

Below being a working example based on a trade impact formula check.

Biggest Trade Increase:=
VAR Fixed_Impact = [Fixed Impact]
VAR EDLP_Impact = [EDLP Impact]
VAR Var_Impact = [Var Impact]
VAR SP_Impact = [SP Impact]
VAR PriceMatch_Impact = [PriceMatch Impact]
VAR Incentive_Impact = [Incentives Impact]
VAR MAX_Value = MAX( MAX( MAX( Fixed_Impact, EDLP_Impact), MAX( SP_Impact, PriceMatch_Impact )), MAX(Incentive_Impact, Var_Impact))
RETURN
    SWITCH( TRUE(),
        Fixed_Impact <> BLANK() && Fixed_Impact = MAX_Value, "Fixed",
        EDLP_Impact <> BLANK() && EDLP_Impact = MAX_Value, "EDLP",
        Var_Impact <> BLANK() && Var_Impact = MAX_Value, "Variable",
        SP_Impact <> BLANK() && SP_Impact = MAX_Value, "Special Promo",
        PriceMatch_Impact <> BLANK() && PriceMatch_Impact = MAX_Value, "PriceMatch",
        Incentive_Impact <> BLANK() && Incentive_Impact = MAX_Value, "Incentives"
    )
Marcus
  • 531
  • 3
  • 2