Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello community,
My data is as follows
CompanyDim
CompanyID | CompanyName |
1 | Company A |
2 | Company A* |
3 | Company B |
4 | Company C |
*The Company A being repeated isn't an error, the company name can be repeated
FactTable
Date | CompanyID | Value |
1/1/2020 | 1 | 200 |
1/1/2020 | 2 | 100 |
1/1/2020 | 3 | 100 |
1/1/2020 | 4 | 50 |
FactTable is joined with the CompanyDim on the CompanyID column
Problem I am trying to solve. I want to rank to see which company has a the highest average for a month based on the company name. For this I have first created two measures
CompanyAvg = AVERAGE(FactTable[Value])
CompanyAvgRank = RANKX (ALLSELECTED(CompanyDim[CompanyName]),CompanyAvg)
When I put these into a visual table on the canvas the results comes out as expected
CompanyName | CompanyAvg | CompanyAvgRank |
Company A | 150 | 1 |
Company B | 100 | 2 |
Company C | 50 | 3 |
I then put a page filter to Filter Out Company A and the result comes out correctly.
CompanyName | CompanyAvg | CompanyAvgRank |
Company B | 100 | 1 |
Company C | 50 | 2 |
I need to now create two new tables, one for Company B and one for Company C and here is where my problem begins. When I use a visual level filter (for Table1 to keep only Company B and for Table2 to keep only Company C) then as I am using ALLSELECTED it shows as Rank1 for each.
How do I modify the measure so that it keeps the page level filter but ignores the visual filter?*
*I do NOT want to hardcode company names in the measure itself
Thanks,
Moiz
Hi @moizsherwani ,
Has your problem been solved? If yes, please consider accept the solution to help the other members find it more quickly.
Best regards,
Lionel Chen
Hi @moizsherwani ,
Like this? I change nothing.
I don’t quite understand the reason for your error. Can you give me a similar screenshot?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @moizsherwani ,
Try this measure:
@camargos88 Thanks and I already tried that but if I do that then it shows me the RANK for Company B to be 2 and Company C to be 3 (the overall rank) even though I want them to be based on whatever is filtered on the page.
I didn't get why you are creating tables for those companies ? Can you explain you target ?
Ricardo
@camargos88 I said table to simplify things, they are actually cards for each company (next to the company logo). The report canvas layout is as follows
COMPANY_B_LOGO CARD
COMPANY_C_LOGO CARD
The Card shows the Avg and I want the highest average i.e. rank = 1 to be turned Green
Hi @moizsherwani ,
Once you are gonna filter the visual with the company name, what is the problem to create 1 measure for each company (you just have 2).
Also, like @v-lionel-msft showed here... you can try using another visual like table.
Another solution would be:
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |