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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sleopol
Frequent Visitor

Top N and others with Date

Good afternoon everyone,

 

I wanted to ask a quick question about finding the top N and others in PowerBI.

 

I followed the tutorial below from SQL BI originally, and the Ranking measure worked fine with the addition of a Top N parameter, and a lookup table so "others" could be included in my model. 

 

Original Video: 

 

https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/

 

 

However, the problem came across when trying to include date in the columns, when I added in date to the columns of my top N visual the ranking itself broke. Due to organizational policies at my company I can't share the file I worked off of, but I will show my measures, and a sample case of the issue. 

 

Ranking1.0 =
IF (
    ISINSCOPE ( 'Product Names'[line] ),
    VAR ProductsToRank = [TopN Value]
    VAR NetValue =
        CALCULATE ( [Sum Net Value], ALLSELECTED ( 'DateKey' ) )
    VAR IsOtherSelected =
        SELECTEDVALUE ( 'Product Names'[line] ) = "Others"
    RETURN
        IF (
            IsOtherSelected,
           
            -- Rank for Others
            ProductsToRank + 1,
           
            -- Rank for regular products
            IF (
                NetValue > 0,
                VAR VisibleProducts =
                    CALCULATETABLE (
                        VALUES ( 'Catalog' ),
                        ALLSELECTED ( 'Product Names' ),
                        ALLSELECTED ( 'DateKey' )
                    )
                VAR Ranking =
                    RANKX (
                        VisibleProducts,
                        CALCULATE (
                            [Sum Net Value],
                            ALLSELECTED ( 'DateKey' )
                        ),
                        NetValue
                    )
                RETURN
                    IF (
                        Ranking > 0 && Ranking <= ProductsToRank,
                        Ranking,
                        0
                    )
            )
        )
)
 
 
 
NewNtValue =
VAR NetValueAll =
    CALCULATE (
        [Sum Net Value],
        REMOVEFILTERS ( 'Product Names' )
    )
RETURN
    IF (
        NOT ISINSCOPE ( 'Product Names'[line] ),

        -- Calculation for a group of products
        NetValueAll,

        -- Calculation for one product name
        VAR ProductsToRank = [TopN Value]
        VAR NetValueOfCurrentProduct = [Sum Net Value]
        VAR IsOtherSelected =
            SELECTEDVALUE ( 'Product Names'[line] ) = "Others"
        RETURN
            IF (
                NOT IsOtherSelected,
               
                -- Calculation for a regular product
                NetValueOfCurrentProduct,

                -- Calculation for Others
                VAR VisibleProducts =
                    CALCULATETABLE (
                        VALUES ( 'Catalog' ),
                        ALLSELECTED ( 'Product Names'[line] )
                    )
                VAR ProductsWithSales =
                    ADDCOLUMNS (
                        VisibleProducts,
                        "@NtValue", [Sum Net Value]
                    )
                VAR NetValueOfTopProducts =
                    SUMX (
                        TOPN (
                            ProductsToRank,
                            ProductsWithSales,
                            [@NtValue]
                        ),
                        [@NtValue]
                    )
                VAR NetValueOthers =
                    NetValueAll - NetValueOfTopProducts
                RETURN
                    NetValueOthers
            )
    )
 
Visible Row =
VAR Ranking = [Ranking1.0]
VAR TopNValue = [TopN Value]
VAR Result =
    IF (
        Ranking <> 0,
        ( Ranking <= TopNValue ) - ( Ranking = TopNValue + 1 )
    )
RETURN
    Result
 
 
TopN Value = SELECTEDVALUE('TopN'[TopN])
 
In the original sample file for including date, the folks over at SQL BI were able to get it to work fine with similar measures to the above (their video is below). 
 
SQL BI Adding date to a top N plus others case:
 
 
 
The below is an example of the issue I'm facing, with some details anonymized. I made sure the others category is visible, and the others are hidden for confidentiality. There are multiple lines that can fall into multiple classes in the model itself. When I go to rank the products with the above measures I get something similar to below. I'll get the others ranking to work fine, but all of my others lines will not rank properly based on their values, and will duplicate in ranking,  while not interacting properly with the Top N selection slicer I have built into the report. I would appreciate any advice the community can give me; thank you and have a great rest of your day!
 
sleopol_0-1667150778532.png

 

1 ACCEPTED SOLUTION
sleopol
Frequent Visitor

Good afternoon everyone, going to close out this forum post with the code that I used that worked for the case I was working with; turns out a simpler approach was the best way to resolve this issue. 

 

Top N =
VAR _topthreecustomerlistwholeyear =
    TOPN (SELECTEDVALUE('TopN'[TopN]),
       
        ALLSELECTED ( 'Product Names'[ProductKey] ),
        CALCULATE ( [Sum Net Value], REMOVEFILTERS ( 'datekey' ) ), DESC
    )
VAR _topthreefilter =
    TOPN ( SELECTEDVALUE('TopN'[TopN]), ALLSELECTED ( 'Product Names'[ProductKey] ), [Sum Net Value], DESC )
VAR _allsales =
    CALCULATE ( [Sum Net Value], REMOVEFILTERS ( 'Product Names' ) )
VAR _othersales =
    _allsales - CALCULATE ( [Sum Net Value], _topthreefilter )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Product Names'[ProductKey] ),
            IF (
                SELECTEDVALUE ( 'Product Names'[ProductKey] ) = "Others",
                _othersales,
                CALCULATE (
                    [Sum Net Value],
                    KEEPFILTERS ( 'Product Names'[ProductKey] IN _topthreecustomerlistwholeyear )
                )
            ),
        [Sum Net Value]
    )

View solution in original post

5 REPLIES 5
sleopol
Frequent Visitor

Good afternoon everyone, going to close out this forum post with the code that I used that worked for the case I was working with; turns out a simpler approach was the best way to resolve this issue. 

 

Top N =
VAR _topthreecustomerlistwholeyear =
    TOPN (SELECTEDVALUE('TopN'[TopN]),
       
        ALLSELECTED ( 'Product Names'[ProductKey] ),
        CALCULATE ( [Sum Net Value], REMOVEFILTERS ( 'datekey' ) ), DESC
    )
VAR _topthreefilter =
    TOPN ( SELECTEDVALUE('TopN'[TopN]), ALLSELECTED ( 'Product Names'[ProductKey] ), [Sum Net Value], DESC )
VAR _allsales =
    CALCULATE ( [Sum Net Value], REMOVEFILTERS ( 'Product Names' ) )
VAR _othersales =
    _allsales - CALCULATE ( [Sum Net Value], _topthreefilter )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Product Names'[ProductKey] ),
            IF (
                SELECTEDVALUE ( 'Product Names'[ProductKey] ) = "Others",
                _othersales,
                CALCULATE (
                    [Sum Net Value],
                    KEEPFILTERS ( 'Product Names'[ProductKey] IN _topthreecustomerlistwholeyear )
                )
            ),
        [Sum Net Value]
    )
v-yalanwu-msft
Community Support
Community Support

Hi, @sleopol ;

Thanks for sharing the file, I've opened the file, but I'm still not clear, what do you want to output, I've seen that your pbix has implemented dynamic topn; Sorry for not having a clear understanding of your specific needs.

vyalanwumsft_0-1667279992334.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Good morning, and thank you for taking a look at the file I uploaded. The issue I'm facing is that my ranking won't display the way the sample file will. In my first screenshot I showed how it's ranking incorrectly despite having the same measures as the attached file. For some reason it will not rank the top N lines per each category in my actual report, but rather seems to rank at random. I can't seem to find a difference in the measures I have posted above in comparison to the sample file I have shared either. Thanks again for the quick replies! 

sleopol
Frequent Visitor

Good morning,

Please find attached an example of what I'm looking for.

 

My model has the same format for the data model with the exception of using date as a slicer for last 6-months. 

 

Could having more lines that fall into multiple categories potentially affect my model? 

 

In the attached example I did notice that each category only has unique products. 

 

All of my measures are based on the attached PBI file on my one drive. 

 

Thanks again for agreeing to look into this! I really appreciate it, and I've been stuck on this issue for a week or two now. 

 

https://1drv.ms/u/s!AmLKI1g9O_1ok3hayWPBUg2P8amQ?e=PwF8kj

 

v-yalanwu-msft
Community Support
Community Support

Hi, @sleopol ;

Sorry you shared too much, I looked a bit confusing and difficult to reproduce your model, due to the sensitivity of your data, can you create some data similar to your model, using a small example and the desired output, it is easier to understand and solve your problem.

please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

Solved: How to upload PBI in Community - Microsoft Power BI Community


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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