DAX - TOP N, BOTTOM N, Sales

[venug20]

Hi Everyone,

1. I want TOP 1 sales by customer based on "State Level"  where Growth 200%.

2. I want TOP 1 sales by customer based on "District Level"  where Growth 200%.

3. I want Bottom 1 Sales by Customer based on "State Level" where Growth 0%.

4. I want Bottom 1 Sales by Customer based on "District Level" where Growth 0%.

Sample data available in this link

Please help on this.... Thaks in advance....

Regards

Venu

[venug20]

Hi Everyone,

Is it possible atleast in this situation.... Please can anyone try

1. I want TOP 1 sales by  "State Level" where Growth 200%.

2. I want TOP 1 sales by "District Level" where Growth 200%.

3. I want Bottom 1 Sales "State Level" where Growth 0%.

4. I want Bottom 1 Sales by "District Level" where Growth 0%.

Please help on this.... Thaks in advance.... Regards Venu

[Thejeswar]

Unable to access the link!!!

[venug20]

@Thejeswar

https://www.dropbox.com/s/eeaptv37lw7jhy8/Cust-Sample-Data.xlsx?dl=0

[Thejeswar]

Hi @venug20,

Take a look at the file with your requirement implemented by clicking here

Hope this solves your need!!!

[venug20]

@v-jiascu-msft @Thejeswar

Thanks for your reply.... I really appreciate your effort...

but i want output like below snapshots....

I want to show one table for TopN Sales and another table for Bottom N Sales....

I am not select any option from Slicer and also i am not doing filters from visual level....

If possible Please provide DAX for this requirement...

[v-jiascu-msft · Accepted solution]

Hi @venug20,

How about this solution? Please check out the demo in the attachment.

Top =
VAR N = 20
VAR ranks =
    RANKX (
        ALL ( Sheet1 ),
        CALCULATE ( SUM ( Sheet1[Sales Value] ) ),
        ,
        desc,
        DENSE
    )
RETURN
    IF (
        MIN ( Sheet1[Data Level] ) = "State level"
            && MIN ( Sheet1[Growth %] ) = 2,
        IF ( ranks <= N, ranks, BLANK () )
    )

Bottom =
VAR N = 20
VAR ranks =
    RANKX ( ALL ( Sheet1 ), CALCULATE ( SUM ( Sheet1[Sales Value] ) ),, asc, DENSE )
RETURN
    IF (
        MIN ( Sheet1[Data Level] ) = "State level"
            && MIN ( Sheet1[Growth %] ) = 0,
        IF ( ranks <= N, ranks, BLANK () )
    )

Best Regards,

Dale

[venug20]

@v-jiascu-msft

You have done a great job, superb.....

thanks alot...

[venug20]

@v-jiascu-msft

I am unable to download attached "PBIX" file...

[v-jiascu-msft]

Hi @venug20,

Please check out the demo in the attachment. The requirements could be achieved by two measures. And then add several slicers.

Top =
RANKX (
    ALLSELECTED ( Sheet1 ),
    CALCULATE ( SUM ( Sheet1[Sales Value] ) ),
    ,
    desc
)

Bottom =
RANKX (
    ALLSELECTED ( Sheet1 ),
    CALCULATE ( SUM ( Sheet1[Sales Value] ) ),
    ,
    asc
)

Best Regards,

Dale