Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am trying to achieve Top 2 states within Top 2 categories. Below is the sample excel data and expected output in image below. I am trying to do the same in Power BI but I couldn't do it. I got the Top 2 categories on the filter pane but I couldn't get Top 2 states from the Top 2 categories using DAX. Can you please help me on DAX?
State | Category | Count |
AZ | A | 9 |
FL | A | 66 |
MN | A | 51 |
IA | A | 57 |
MO | A | 1 |
CO | A | 47 |
NV | A | 21 |
VA | A | 16 |
WA | A | 40 |
AZ | B | 57 |
FL | B | 28 |
MN | B | 74 |
IA | B | 18 |
MO | B | 18 |
CO | B | 3 |
NV | B | 67 |
VA | B | 3 |
WA | B | 61 |
AZ | C | 14 |
FL | C | 22 |
MN | C | 20 |
IA | C | 37 |
MO | C | 45 |
CO | C | 53 |
NV | C | 72 |
VA | C | 15 |
WA | C | 14 |
Thanks for your help.
Solved! Go to Solution.
Please try this measure expression in your visual. Note that it looks like the top two categories are A and B from your sample data. Maybe I misunderstood your desired logic, and you can adapt the measure expression below if incorrect.
Top 2 of Top 2 =
VAR vCategoryTable =
ADDCOLUMNS (
ALL ( TopTwo[Category] ),
"cCount", CALCULATE ( SUM ( TopTwo[Count] ), ALL ( TopTwo[State] ) )
)
VAR vTop2Cats =
TOPN ( 2, vCategoryTable, [cCount], DESC )
VAR vStateTable =
ADDCOLUMNS (
ALL ( TopTwo[State] ),
"cStateCount", CALCULATE ( SUM ( TopTwo[Count] ), vTop2Cats )
)
VAR vTop2States =
TOPN ( 2, vStateTable, [cStateCount], DESC )
RETURN
CALCULATE (
SUM ( TopTwo[Count] ),
KEEPFILTERS ( vTop2Cats ),
KEEPFILTERS ( vTop2States )
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi All,
Below is what I have updated & it works for filtering. Just for reference. Thanks @mahoneypat
Top 2 of Top 2 =
VAR vCategoryTable =
ADDCOLUMNS (
ALL ( TopTwo[Category] ),
"cCount", CALCULATE ( SUM ( TopTwo[Count] ), FILTER(ALL ( TopTwo[State] ),TopTwo[Category]<>BLANK() && TopTwo[Category]<>"D" ))
)
VAR vTop2Cats =
TOPN ( 2, vCategoryTable, [cCount], DESC )
VAR vStateTable =
ADDCOLUMNS (
ALL ( TopTwo[State] ),
"cStateCount", CALCULATE ( SUM ( TopTwo[Count] ), vTop2Cats )
)
VAR vTop2States =
TOPN ( 2, vStateTable, [cStateCount], DESC )
RETURN
CALCULATE (
SUM ( TopTwo[Count] ),
KEEPFILTERS ( vTop2Cats ),
KEEPFILTERS ( vTop2States )
)
Glad you got it worked out. Thanks for posting final expression.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this measure expression in your visual. Note that it looks like the top two categories are A and B from your sample data. Maybe I misunderstood your desired logic, and you can adapt the measure expression below if incorrect.
Top 2 of Top 2 =
VAR vCategoryTable =
ADDCOLUMNS (
ALL ( TopTwo[Category] ),
"cCount", CALCULATE ( SUM ( TopTwo[Count] ), ALL ( TopTwo[State] ) )
)
VAR vTop2Cats =
TOPN ( 2, vCategoryTable, [cCount], DESC )
VAR vStateTable =
ADDCOLUMNS (
ALL ( TopTwo[State] ),
"cStateCount", CALCULATE ( SUM ( TopTwo[Count] ), vTop2Cats )
)
VAR vTop2States =
TOPN ( 2, vStateTable, [cStateCount], DESC )
RETURN
CALCULATE (
SUM ( TopTwo[Count] ),
KEEPFILTERS ( vTop2Cats ),
KEEPFILTERS ( vTop2States )
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat ,
Thanks for the reply. Yes you are correct, It should be A & B. I was using Random values in the excel so the charts did not show up correctly. The above solution works 🙂 Thanks. But I need to exclude some of the categories ( Blank(),D,..) before calculating this measure. How can I exclude in the above measure. Please help.
Hi All,
Below is what I have updated & it works for filtering. Just for reference. Thanks @mahoneypat
Top 2 of Top 2 =
VAR vCategoryTable =
ADDCOLUMNS (
ALL ( TopTwo[Category] ),
"cCount", CALCULATE ( SUM ( TopTwo[Count] ), FILTER(ALL ( TopTwo[State] ),TopTwo[Category]<>BLANK() && TopTwo[Category]<>"D" ))
)
VAR vTop2Cats =
TOPN ( 2, vCategoryTable, [cCount], DESC )
VAR vStateTable =
ADDCOLUMNS (
ALL ( TopTwo[State] ),
"cStateCount", CALCULATE ( SUM ( TopTwo[Count] ), vTop2Cats )
)
VAR vTop2States =
TOPN ( 2, vStateTable, [cStateCount], DESC )
RETURN
CALCULATE (
SUM ( TopTwo[Count] ),
KEEPFILTERS ( vTop2Cats ),
KEEPFILTERS ( vTop2States )
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |