DAX Calculation: Percent of First (or Max) Value

[Anonymous]

Hello People

I'm stuck in DAX trying to figure out how to make each row of this table Calculate the current row's sum vs the sum of the first row. It's essentially a shopping cart funnel. The first value is 100%, and each subsequent value is less than the original.  The problem I can't seem to get around is that I can count EventTypeId=0 (the first value of 17,342) but for the next row where EventTypeId=1 I can't escape the current context to divide 1,221 by 17,342.  

I could calculate the 100% in the first row, but for the next row, i'd be dividing 1,221 by null.  How can I divide each EventType count by the value of the first row (17,342)? 

Here's my best current effort (which doesn't yet indlude the division): 

  PercentVsMax = Calculate (Sum(ShoppingEvents[EventCount]), ShoppingEvents[EventTypeId]=0)

[MFelix]

Hi @Anonymous,

Use the following formula:

PercentVsMax =
DIVIDE (
    SUM ( Events[Count] ),
    CALCULATE ( MAX ( Events[Count] ), ALLSELECTED ( Events[Event] ) )
)

This will always calculate based on the max value for the selected Items if you need to have it calculated based on a specific event name do somethin like this:

PercentVsEvent =
DIVIDE (
    SUM ( Events[Count] ),
    CALCULATE ( MAX ( Events[Count] ), Events[Event] = "D" )
)

Below you can see both result in the table.

Regards,

MFelix

[Anonymous]

That didn't work for me. The denominator portion of the division is fine, but the numerator isn't working right with my data set. I think I MAX isn't working as expected with my set and I need to find a way to SUM for EventTypeId=0. 

 I

[MFelix]

Hi @Anonymous,

Try to make this change to your measure:

PercentVsMax =
DIVIDE (
    CALCULATE(SUM ( Events[Count] ), Events[EventTypeId=0),
    CALCULATE ( MAX ( Events[Count] ), ALLSELECTED ( Events[Event] ) )
)

Regards,

MFelix

[Anonymous]

Hi MFelix 

Sorry for misleading you, but in my last response I confused Numerator and Denominator. The Numerator of SUM ( ShoppingEvents[EventCount] ) works correctly as you can see in the screen shot, but the denominator: CALCULATE ( MAX ( ShoppingEvents[EventCount] ), ShoppingEvents[EventCount] )  isn't working as expected.  

Here's a screen capture with the formula and the results for each part of the expression: 

PercentVsMax =
DIVIDE (
SUM ( ShoppingEvents[EventCount] ),
CALCULATE ( MAX ( ShoppingEvents[EventCount] ), ShoppingEvents[EventCount] )
)

The Denominator should be 17,342 for all rows. 

[Anonymous · Accepted solution]

I managed to solve my issue. While looking at a normal SUM of events, I did a Power BI Quick Calculation which did nearly what I needed it to do: 

EventCount % difference from User viewed a product =
VAR __BASELINE_VALUE =
CALCULATE(
SUM('ShoppingEvents'[EventCount]),
'ShoppingEvents'[EventType] IN { "User viewed a product" }
)
VAR __MEASURE_VALUE = SUM('ShoppingEvents'[EventCount])
RETURN
DIVIDE(__MEASURE_VALUE, __BASELINE_VALUE)

The only thing that I don't understand is that I can't seem to change this line: 

'ShoppingEvents'[EventType] IN { "User viewed a product" } 

to

'ShoppingEvents'[EventTypeId] = 0

If i change this line, I'll only get a value for the first row. The two seem like they should produce identical results to me. 

[MFelix]

Hi @Anonymous,

If you can define that you want your baseline to be event 0 then you just need to change the formula I gave to:

PercentVsMax = 
DIVIDE (
    CALCULATE(SUM ( Events[Count] )),
   CALCULATE(SUM(Events[Count]),Events[EventTypeID]=0 ))

No need to additional fancy formulas since you are making a filter of the calculate on your measure so it will always return the total sum of that event, if you want to always use that event no matter if this EventTypeID is select on your reports you can change the formula to this:

PercentVsMax =
DIVIDE (
    CALCULATE ( SUM ( Events[Count] ) ),
    CALCULATE ( SUM ( Events[Count] ), Events[EventTypeID] = 0, ALL ( Events[EventTypeID] ) )
)

The ALL in the formula removes all filter used in the report and allows you to always have the same baseline.

Regards,

MFelix

[Anonymous]

Your way works, but the thing I've figured out is that it only displays the value when EventTypeId is displayed in the table.  If EventType is displayed, then the column total seems to display the correct result, but only the first row displays a non-blank value. Is this the expected behavour? 

[MFelix]

Hi @Anonymous,

This should give you the same value on all rows not only on the first row.

How do you have set up your measures?

Regards,

MFelix

[MFelix]

Hi @Anonymous,

Add a table with the following code:

Total_By_Event =
SUMMARIZE (
    ALL ( Events[Count], Events[Event] ),
    Events[Event],
    "Count", SUMX ( Events, Events[Count] )
)

Then just change your measure to:

PercentVsMax = 
DIVIDE (
    CALCULATE(SUM ( Events[Count] )),
   MAX(Total_By_Event[Count]))

Final result below:

Hope this helps,

Regards,

MFelix

[Anonymous]

Thanks for the help. I have something that's working, but I tried this anyway. It's not producing the expected result. 

One super weird thing: Using the summarize table, for the first row, I get 126,780. When I use my normal dataset I get a value that's two smaller (126,778). All other rows match. I wonder what's going on with Summarize that adds two values?