Filter to show Top Stores with a specified combined sales

[Ibrahimashoor]

Hi all, 

I am trying to find away to show the top stores that represnts a  combined sales of 10B$ for example

Extra: would be to make this combines sales a whatIf parameter (that user can change from 1 , 2, ...10B)

Thank you in advanceT

[Ashish_Mathur · Accepted solution]

Hi,

Share the link from where i can download your PBI file.

[Ashish_Mathur · Accepted solution]

Hi,

This screenshot should say it all.  I have applied a filter with the criteria of <=3000000000.  These are the measures i used

Rank of Stores by total sales = if(HASONEVALUE(Stores[StoreName]),RANKX(ALL(Stores[StoreName]),[Total Sales]),BLANK())

Cumulative sales = SUMX(TOPN([Rank of Stores by total sales],CALCULATETABLE(VALUES(Stores[StoreName]),ALL(Stores[StoreName])),[Total Sales]),[Total Sales])

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Share the link from where i can download your PBI file.

[Ibrahimashoor]

Thank you very much. I have put the file in OneDrive (I couldn't load it here idrectly)

https://1drv.ms/u/s!AibcI9O4iXjagYNCJ3-Qrsl1xRYa8A?e=bvBtnU 

It is the standard Microsoft Contososales file

[Mariusz · Accepted solution]

Hi @Ibrahimashoor 

Try this

Measure = 
VAR __sales =
    ADDCOLUMNS(
        ALL( Stores[StoreName] ),
        "@sales", [Total Sales] 
    )
VAR __stors = 
SELECTCOLUMNS(
    FILTER(
        __sales,
        SUMX( 
            FILTER( 
                SELECTCOLUMNS( __sales, "@@sales", [@sales] ), 
                [@sales] < [@@sales] 
            ), 
            [@@sales] ) > MAX( 'selections'[selection] )
    ),
    "StoreName", Stores[StoreName]
)
RETURN 
    CALCULATE(
        [Total Sales],
        KEEPFILTERS( NOT Stores[StoreName] IN __stors )
    )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Ashish_Mathur · Accepted solution]

Hi,

This screenshot should say it all.  I have applied a filter with the criteria of <=3000000000.  These are the measures i used

Rank of Stores by total sales = if(HASONEVALUE(Stores[StoreName]),RANKX(ALL(Stores[StoreName]),[Total Sales]),BLANK())

Cumulative sales = SUMX(TOPN([Rank of Stores by total sales],CALCULATETABLE(VALUES(Stores[StoreName]),ALL(Stores[StoreName])),[Total Sales]),[Total Sales])

Hope this helps.