I have the following table:
PRODUCT | REGION | REP | MONTH |
---|---|---|---|
REG-YH-67 | NORTH | JANE | JUNE |
REG-YH-67 | NORTH | JANE | MARCH |
REG-YH-67 | NORTH | JANE | DECEMBER |
REG-YH-89 | NORTH | JANE | SEPTEMBER |
REG-YH-67 | OUT-OF-REG | JANE | JUNE |
REG-YH-9 | NORTH | JANE | MARCH |
REG-YH-9 | WEST | ALEX | DECEMBER |
REG-YH-9 | WEST | ALEX | SEPTEMBER |
REG-YH-30 | WEST | WILL | JUNE |
REG-YH-33 | WEST | WILL | MARCH |
REG-YH-33 | OUT-OF-REG | WILL | DECEMBER |
REG-YH-33 | WEST | WILL | SEPTEMBER |
REG-YH-33 | WEST | MARTHA | JUNE |
REG-YH-33 | WEST | MARTHA | MARCH |
REG-YH-67 | OUT-OF-REG | JANE | DECEMBER |
REG-YH-67 | OUT-OF-REG | JANE | SEPTEMBER |
REG-YH-9 | NORTH | JANE | JUNE |
REG-YH-89 | NORTH | JANE | MARCH |
REG-YH-9 | NORTH | JANE | DECEMBER |
REG-YH-9 | WEST | JANE | SEPTEMBER |
REG-YH-9 | OUT-OF-REG | JANE | JUNE |
REG-YH-9 | NORTH | JANE | MARCH |
REG-YH-67 | OUT-OF-REG | JANE | DECEMBER |
REG-YH-9 | NORTH | JANE | SEPTEMBER |
REG-YH-67 | OUT-OF-REG | JANE | JUNE |
REG-YH-9 | OUT-OF-REG | JANE | MARCH |
This is the source file that I use in a power bi doc that I want to create, I have 2 global filters in my main page which are REP
and MONTH
, and I want to learn how to use DAX to create a summary table that will plot the top 3 best selling PRODUCTs
for each REGION
If we do not use the filters then the desired table would look like this:
REGION | BEST SELLING | 2ND BEST SELLING | 3RD BEST SELLING |
---|---|---|---|
NORTH | REG-YH-9 | REG-YH-67 | REG-YH-89 |
OUT-OF-REG | REG-YH-67 | REG-YH-9 | REG-YH-33 |
WEST | REG-YH-33 | REG-YH-9 | REG-YH-30 |
But I want to be able to use my global filters in the PBI dashboard and perhaps filter by REP
= MARTHA & JANE so that my summary table would recalculate/turn to be:
REGION | BEST SELLING | 2ND BEST SELLING | 3RD BEST SELLING |
---|---|---|---|
NORTH | REG-YH-9 | REG-YH-67 | REG-YH-89 |
OUT-OF-REG | REG-YH-67 | REG-YH-9 | null |
WEST | REG-YH-33 | REG-YH-9 | null |
Is there a way to do this on Power BI?
Create 3 measures changing the rank to 1,2 or 3
Measure =
VAR tbl =
ADDCOLUMNS(
'table'
,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT],'table'[REGION])), , ,Dense)
)
VAR temp = SELECTCOLUMNS( FILTER(tbl, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result = CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp)
RETURN result
Below solution accounts for row totals if this is important.
Measure1 =
VAR tbl1 =
ADDCOLUMNS(
'table'
,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT],'table'[REGION])), , ,Dense)
)
VAR temp1 = SELECTCOLUMNS( FILTER(tbl1, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result1 = CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp1)
VAR tbl2 =
ADDCOLUMNS(
'table'
,"@rank",RANKX(VALUES('table'[PRODUCT]),CALCULATE( COUNT('table'[PRODUCT]), ALLEXCEPT('table','table'[PRODUCT])), , ,Dense)
)
VAR temp2 = SELECTCOLUMNS( FILTER(tbl2, [@rank] = 1),"a", 'table'[PRODUCT])
VAR result2 = CALCULATE(MAX('table'[PRODUCT]),'table'[PRODUCT] IN temp2)
RETURN IF(ISFILTERED('table'[REGION]), result1, result2)