Sum of TOPN

[Anonymous]

Hey guys,

I've watched this youtube video back in a while and I wondered if would it be possible to have both the “Others” information AND the TOPN that is being filtered. For instance, in the image attached below I show what I aim to calculate, but I could not find a way to calculate it yet, so here I am 🙂 

The measures is as follows

First off, a parameter is created:

TopN = GENERATESERIES ( 1, 20, 1 )

Then, the Others name was created and appended into the original table:

Product Names = UNION ( ALLNOBLANKROW ( 'Product'[Product Name] ), { "Others" } )

Afterwards, a visible row measure is created in order to only show those lines that are smaller than or equal to the value in the parameter previously created (this is set into the Filter panel of the table):

Visible Row = 
VAR Ranking = [Ranking]
VAR TopNValue = [TopN Value]
VAR Result =
    IF ( NOT ISBLANK ( Ranking ),
        ( Ranking <= TopNValue ) - ( Ranking = TopNValue + 1 ))
RETURN Result

The last step was to create the measure that computes the total for “Others”:

Sales Amt = 
VAR SalesOfAll =
    CALCULATE ( [Sales Amount],
        REMOVEFILTERS ( 'Product Names' ))
RETURN IF ( NOT ISINSCOPE ( 'Product Names'[Product Name] ),

        -- Calculation for a group of products 
        SalesOfAll,

        -- Calculation for one product name
        VAR ProductsToRank = [TopN Value]
        VAR SalesOfCurrentProduct = [Sales Amount]
        VAR IsOtherSelected =
            SELECTEDVALUE ( 'Product Names'[Product Name] ) = "Others"
        RETURN
            IF (
                NOT IsOtherSelected,
                
                -- Calculation for a regular product
                SalesOfCurrentProduct,

                -- Calculation for Others
                VAR VisibleProducts =
                    CALCULATETABLE (
                        VALUES ( 'Product' ),
                        ALLSELECTED ( 'Product Names'[Product Name] )
                    )
                VAR ProductsWithSales =
                    ADDCOLUMNS (
                        VisibleProducts,
                        "@SalesAmount", [Sales Amount]
                    )
                VAR SalesOfTopProducts =
                    SUMX (
                        TOPN (
                            ProductsToRank,
                            ProductsWithSales,
                            [@SalesAmount]
                        ),
                        [@SalesAmount]
                    )
                VAR SalesOthers = 
                    SalesOfAll - SalesOfTopProducts
                RETURN
                    SalesOthers))

 Now I ask: do someone knows how to calculate the TOPN filtered to show it along with the Others in the same table?

Thanks in advance!

Pbi file here 

[SpartaBI · Accepted solution]

@Anonymous here is a link to download the file with the solution:
C0120 - Filter top 3 products with a row for others using DAX in Power BI.pbix

I took the file from their article and added the logic to also show the TopN filtered. 

      

Showcase Report – Contoso By SpartaBI

[Pedro503]

Hey Sparta, how are you doing?

Do you know how to incorporate an Excel file into the power bi.pbix file? I mean, there's no separate database, but only one attached to the file. Do you know how to do that?

I have a database that I want to make it to be “inside” my .pbix file, but I don't know how to do that.

To illustrate my point, take this image into consideration:

Thanks in advance.

[Anonymous]

This is exactly what I was looking for, buddy. Thanks a lot for the solution you provided

[SpartaBI]

@Anonymous welcome 🙂

[v-shex-msft]

HI @Anonymous,

For your requirement, I'd like to suggest you create a variable with summarize function to summary table records with categories and calculations.

Then you can simply use the iterator function(e.g. sumx, averagex) and filter conditions to apply the second aggreated and filter effects on it.

Reference links:

Measure Totals, The Final Word - Microsoft Power BI Community

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup.com)

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for your support, buddy 🤝