Quartile Calculation

[mbagwan]

Can someone please help me to calculate

Percentage of customers that contribute the 1st quartile (top 25%) of revenue 

Percentage of customers that contribute the 2nd quartile (25%-50%) of revenue

Percentage of customers that contribute the 3rd quartile (50%-75%) of revenue

Percentage of customers that contribute the 4rd quartile (75%-100%) of revenue  

Basically, I need to show a bar chart which will display 4 bars for each quartile as mentioned above.

Thanks

[Ashish_Mathur]

Hi,

Please share some data to work with.

[v-alq-msft · Accepted solution]

Hi, @mbagwan 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Quartile:

You may create a measure as below.

Percentage = 
var total = CALCULATE(DISTINCTCOUNT('Table'[CustomerID]),ALL('Table'))
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Rank",
    RANKX(
        ALL('Table'),
        [Revenue Contribution],,ASC
    )
)
var newtab = 
SUMMARIZE(
    Quartile,
    Quartile[Quartile],
    "Count",
    SWITCH(
        [Quartile],
        "quartile1",
        DIVIDE(
            COUNTROWS(
                FILTER(
                    tab,
                    [Rank]>=1&&[Rank]<total*0.25
                )
            ),
            total
        ),
        "quartile2",
        DIVIDE(
            COUNTROWS(
                FILTER(
                    tab,
                    [Rank]>=total*0.25&&[Rank]<total*0.5
                )
            ),
            total
        ),
        "quartile3",
        DIVIDE(
            COUNTROWS(
                FILTER(
                    tab,
                    [Rank]>=total*0.5&&[Rank]<total*0.75
                )
            ),
            total
        ),
        "quartile4",
        DIVIDE(
            COUNTROWS(
                FILTER(
                    tab,
                    [Rank]>=total*0.75&&[Rank]<=total            )
            ),
            total
        )
    )
)
return
SUMX(
    newtab,
    [Count]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mbagwan]

Hi, please note that the CustomerID and Revenue are in two different tables as per my data.

Could you please help on that

[amitchandak]

@mbagwan , I think you have Box and Whisker chart for that. See if that can help

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380831?tab=Overview

refer for quartile

https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

[mbagwan]

I tried but that's not working