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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hoyt22
Regular Visitor

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:

 

https://drive.google.com/drive/folders/1d3Ps5PquH1gdCk-gs2B0Yk2NrLku6uDN?usp=sharing

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
Regular Visitor

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.