Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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)

 

Screen

FXLGb4r - Imgur.png

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

View solution in original post

10 REPLIES 10
MFelix
Super User
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.

 

Percente_Max.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

 

 Ipbi.PNG

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

 

pbi.PNG

Anonymous
Not applicable

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. 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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? 

 

pbi.PNG

pbi2.png

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

summarize.png

 

Hope this helps,

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

 

pbi.PNG

 

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? 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.