Forum Discussion
DAX Calculation: Percent of First (or Max) Value
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)
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.
10 Replies
- MFelix
Super User
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
- 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
