Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
hoyt22
Helper I
Helper I

Issue with Rankings and Slicers on Time Series Chart

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]

1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

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:

vyohuamsft_0-1716951744057.png

vyohuamsft_1-1716951845124.png

 

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.

View solution in original post

3 REPLIES 3
hoyt22
Helper I
Helper I

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.

v-yohua-msft
Community Support
Community Support

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:

vyohuamsft_0-1716951744057.png

vyohuamsft_1-1716951845124.png

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors