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.
I have a matrix with Market and Advertiser in the Rows and several Ranking measures in the Values. The advertisers are ranked by spend, impressions, and TRPs. To create the Ranking measures, I used RANKX and ALLSELECTED.
Example:
Rank_Spend =
IF(
[TotalSpend] > 0,
RANKX(
ALLSELECTED('50_Floor_Daily_Local_Market_TV'[ADVERTISER]),
[TotalSpend],
,DESC,
Dense)
)
I have an AggregateTV column that adds up all the rankings and finds the average. I have this ignoring any blank ranks.
AggregateTV =
VAR RankList = {
[Rank_Spend],
[Rank_IMPs],
[Rank_TRPs],
[Rank_Spend_15],
[Rank_Spend_3060],
[Rank_Spend_Daytime]
}
VAR SumOfRanks =
SUMX(
RankList,
IF(NOT(ISBLANK([Value])), [Value], BLANK())
)
VAR CountOfNonBlankRanks =
COUNTAX(
FILTER(
RankList,
NOT(ISBLANK([Value]))
),
[Value]
)
RETURN
DIVIDE(SumOfRanks, CountOfNonBlankRanks, BLANK())
I also have a filter on the matrix so that only the top 10 advertisers by spend in each market is displayed in the matrix.
The matrix seems to be working fine. However, I need to also have a time series showing the ranking of advertisers over time. I have Date in my X-axis and my AggregateTV measure in the Y-axis. I have a date, market, and advertiser slicer.
The issue is with the advertiser slicer. When all advertisers are selected, the data displayed in the line chart is correct. If I select one or just a few, the data is incorrect. Ultimately, I want to be able to select one or more advertisers and have it affect which advertisers are shown in the line chart but not have the AggregateTV values affected. I've tried to select the Advertiser slicer, choose Edit Interactions, and click None. But then the slicer doesn't affect the advertisers shown.
I hope that's clear, but please let me know if there are any questions!
Link to PowerBI and csv file:
[removed]
Solved! Go to Solution.
HI, @hoyt22
In Power BI, when you select one or more vendors in the slicer, it changes the context in the data model, which affects the calculation of your measures. That's why the data you see when you select one or a few vendors is different from the data you see when you select all slicers.
Your AggregateTV measure are calculated based on the current context. If you want to make the AggregateTV measure unaffected by the slicer, you can try using the ALL function in your measure to remove all filtering for the vendor column.
I help you change the ALLSELECT of the measures in the RANK series to the ALL function, here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply! I'm reviewing the file now, and it seems to work!
So, the only thing you changed was using ALL instead of ALLSELECTED in all my rank measures?
Hi, @hoyt22
Yes, as I explained above. AggregateTV measure is calculated based on the current context. Use the ALL function in a measure to remove all filters for the vendor column.
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI, @hoyt22
In Power BI, when you select one or more vendors in the slicer, it changes the context in the data model, which affects the calculation of your measures. That's why the data you see when you select one or a few vendors is different from the data you see when you select all slicers.
Your AggregateTV measure are calculated based on the current context. If you want to make the AggregateTV measure unaffected by the slicer, you can try using the ALL function in your measure to remove all filtering for the vendor column.
I help you change the ALLSELECT of the measures in the RANK series to the ALL function, here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
21 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |