Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need a formula that gives me the result like the pivot tables:
Solved! Go to Solution.
You need something like this:
[# Attendees] =
SUMX(
VALUES( Attendees[AttendeeIdentifier] ),
CALCULATE(
MAX( Attendees[#Attendees] )
)
)
even though... when I look at the first visual, it looks like you are also taking into consideration the region since the total is 126. The measure above will not give you 126, it'll return 63. However, if you change the measure so that it does return 126 for this visual... it'll not return 63 for the second visual. It'll return 126.
So, in a word, it looks like either your defnition is not consistent or... you want 1 measure to behave differently depending on whether there is a filter on region or not. However, please note that while the individual cells in a table can have a filter on region, the totals won't (for the majority of cases) and hance you won't be able to do what you want to do according to what your visuals show.
Best
D
You need something like this:
[# Attendees] =
SUMX(
VALUES( Attendees[AttendeeIdentifier] ),
CALCULATE(
MAX( Attendees[#Attendees] )
)
)
even though... when I look at the first visual, it looks like you are also taking into consideration the region since the total is 126. The measure above will not give you 126, it'll return 63. However, if you change the measure so that it does return 126 for this visual... it'll not return 63 for the second visual. It'll return 126.
So, in a word, it looks like either your defnition is not consistent or... you want 1 measure to behave differently depending on whether there is a filter on region or not. However, please note that while the individual cells in a table can have a filter on region, the totals won't (for the majority of cases) and hance you won't be able to do what you want to do according to what your visuals show.
Best
D
HI @rmeng ,
Instead of MAX, try using SUM.
This should give you the answer.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
What I want is formula when I filter by Country that returns the max [#Attendee] of AAA ([attendeeidenfier]).
So in this case Argentina only have 1 Attendee instead of 2 for the first two rows.
Te total shoulb be When I filter by Argentina = 63 when I filter by LATAM = 63 and BIR = 63.
PS: I have multiple other dimensios in this table
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |