How to create a calculated measure that shows how many group names that need to make 80%?

[bikelley]

Hello, 

I am trying to calculate how many group names that will take 80%. I found a way but it did not work. I am sure there is an easy way to do that. Please image below, if you order Market share highest to lowest, it will take top 6 Group Names to get 80% (Total come up to 88.11%, my mark is 80%, if you sum of top 5 it will not make 80%) 

Can anybody please tell me how to get it or is there any easy way? 

What I tried,  (I am getting 4 not 6) 

# Of Companies = 

VAR TotalSales = CALCULATE(SUM(GroupedSuppliers[VolumeSold]),GroupedSuppliers)
VAR ThreesholdPercentage = 0.8
RETURN
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    VALUES(GroupedSuppliers[GroupName] ),
                    "TotalSalesOuter", [TotalVolumeSold]
                ),
                "CumulatedPercentage",
                DIVIDE(
                    SUMX(
                        FILTER(
                                ADDCOLUMNS(
                                    VALUES( GroupedSuppliers[GroupName] ),
                                    "TotalSalesInner", [TotalVolumeSold]
                                ),
                                [TotalSalesInner] >= [TotalSalesOuter]
                        ),
                        [TotalSalesInner]
                    ),
                    TotalSales
                )
            ),
            GroupedSuppliers
        ),
        [CumulatedPercentage] <= ThreesholdPercentage
    )
)

Sample file

https://drive.google.com/file/d/1jWUHbT2h_AYV2cdUDX67T0v-GJ5r7_q2/view?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[bikelley]

@Ashish_Mathur 

Hi, I had an issue, and I totally forgot to tell you that I am using Direct Query. 

The issue is that the formula below is not working in Direct Query mode, Is there any way we can change this to work in Direct Query? Everything else is good to do

Order_GroupName = RANKX(all(GroupedSuppliers[GroupName]),GroupedSuppliers[GroupName],,0)

Thank you So much

[Ashish_Mathur]

Hi,

That is a calculated column formula which is required to account for ties when ranking.  If there are no ties in your actual data then you may remove this calculated column formula.

[bikelley]

Hi @Ashish_Mathur ,

Unfortunately, it is tight to few formulas. 

Min order number = MIN(GroupedSuppliers[Order_GroupName])

Cumulative volumeSold = SUMX(TOPN([Rank of GroupName by volume sold],CALCULATETABLE(VALUES(GroupedSuppliers[GroupName]),ALL(GroupedSuppliers[GroupName])),[TotalVolumeSold]*1000+[Min order number]),[TotalVolumeSold])

and then above formal tight to some others,

I have tried removing it, it did not work. It is showing 5 instead of 6.

Is there any other work around for this?

Thank you so much for your help.

[Ashish_Mathur]

Hi,

The result is 5 because of a tie in Volume sold of 1000,000 (Cool Pit and Killer Bee).  To give them a different rank, the calculated column formula is required.

[bikelley]

@Ashish_Mathur 

​Thanks, but the calculated column with RANKX does not support Direct query right. I double checked online, there used to be an option called ​ "allow unrestricted measures", but it does not have now. Do you suggest anything else for this situation with Direct query? Sorry to keep bothering you tho. 

Thank you 

[Ashish_Mathur]

Hi,

I don't think I can solve this without the calculated column formula.  May be someone else can help. 

[bikelley]

@Ashish_MathurThank you for the help. I appreciate your help . 

[bikelley]

Thank you so much for showing another way. It works the way I want it. I really appreciate your time and help. Again thank you! 

[Ashish_Mathur]

You are welcome.