Formula to calculate number of contribution to percentage to total

[ktt777]

Hi all,

i have a data contain spend per company in different countries. 

How do i write a dax formula to calculate number of top high spending company that contribute to 50% of total spend in each country?

Here is link to data example Percentage.xlsx

thanks, 

[Anonymous · Accepted solution]

Hi @ktt777 , hello Ashish_Mathur and elitesmitpatel, thank you for your prompt reply!

In order to calculate the number of high-expenditure companies that account for 50 per cent of total expenditures in each country, please try as following:

• Create a measure to get the CumulativePercentage for different countries:

CumulativePercentage = 
VAR CurrentCountry = MAX('Table'[Country]) 
VAR CurrentTotalSpend = MAX('Table'[TotalSpend]) 
VAR TotalSpendByCountry = CALCULATE(SUM('Table'[TotalSpend]), ALLEXCEPT('Table', 'Table'[Country])) 
VAR RunningTotal = CALCULATE(
   SUM('Table'[TotalSpend]),
   FILTER(
       ALLEXCEPT('Table', 'Table'[Country]), 
       'Table'[TotalSpend] >= CurrentTotalSpend 
   )
)
RETURN
DIVIDE(RunningTotal, TotalSpendByCountry, 0) 

• Then create a rownumber measure to get the closest percentage to 50%

RowNumber = RANKX(ALLEXCEPT('Table','Table'[Country]),ABS([CumulativePercentage]-0.5),,ASC)

• Lastly, create two measures you need:

Total = CALCULATE('Table'[CumulativePercentage],FILTER('Table','Table'[RowNumber]=1))

No of supplier = 

VAR CurrentCountry = MAX('Table'[Country]) 

RETURN

IF(

    [RowNumber] = 1, 

    RANKX(

        FILTER(

            ALL('Table'), 

            'Table'[Country] = CurrentCountry 

        ),

        'Table'[Percentage], 

        ,

        DESC, 

        DENSE 

    ),

    BLANK() 

)
 

 Result for your reference:

Best regards,

Joyce

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

[ktt777]

hi, 

i updated example with expected results. Percentage.xlsx

[Ashish_Mathur]

Hi,

In another tab of that file, show the expected result.

[ktt777]

sorry i did not find the 2nd tab

[Ashish_Mathur]

That is not what i asked for.  Create another tab and show the expected result there.

[ktt777]

thank you. 

what i can to see is for example below. there are 11 companies at the top contribute to 50% of total spend. 

How to create formula to count to get 11 companies? There maybe can not find the exact 50% to total spend, but the closest to 50%

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[elitesmitpatel]

I have solved it please check the below file. 
Percentage _solved 

Please appreciate my work if it helps you.

[elitesmitpatel]

Hi @ktt777 

use this dax:
 C2/ SUM($C$2:$C$38)