Cumulative percentage based on sub-groups (filterable)

[Mister_T]

Dear community,

for the following task, I was able to find some solutions for some single steps, but unfortunately did not manage to combine those for reaching my desired goal. I hope you can help.

I have the following data table 'Reseller Revenue':

 

Reseller	Sales_Country	Revenue [EUR]
A-Company	Russian Federation	310.731
A-Company	United States	205.094
A-Company	Germany	35.000
B-Company	Brazil	159.500
C-Company	France	128.660
C-Company	Brazil	108.000
C-Company	Russian Federation	33.600
D-Company	Germany	105.730
D-Company	Brazil	104.219
D-Company	United States	96.392
D-Company	India	88.748
D-Company	Italy	27.090
E-Company	Brazil	104.219
E-Company	Germany	76.600
E-Company	France	13.680
F-Company	Russian Federation	78.290
F-Company	Germany	53.200
F-Company	Italy	30.000
F-Company	United States	29.666
G-Company	France	52.500
H-Company	Germany	48.270
H-Company	Canada	13.245
I-Company	France	35.000
J-Company	Austria	12.630
K-Company	France	4.290
	Total	1.954.353

Now, I want to get a table visual as shown below, which automaticaly updates in case I am for example filtering only for specific sales countries in the original table (for example all countries except United States):

Could you please help me in drafting the steps I have to take? Especially the cumulative sum of the percentages of the summarized revenue per reseller is giving me a hard time so far.

Thank you very much for your support!

[v-rzhou-msft · Accepted solution]

Hi @Mister_T 

Please try my measure.

% of Total = 
VAR _Sum = SUM('Reseller Revenue'[Revenue [EUR]]])
VAR _Total = SUMX(ALLSELECTED('Reseller Revenue'),'Reseller Revenue'[Revenue [EUR]]])
Return
DIVIDE(_Sum,_Total)

Cumulative = 
VAR b =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Reseller Revenue'[Reseller ] ),
            ALL ( 'Reseller Revenue'[Reseller ] )
        ),
        "_1", [% of Total]
    )
VAR a = [% of Total]
RETURN
    SUMX ( FILTER ( b, [_1] >= a ), [_1] )

Result is as below.

Best Regards,

Rico Zhou

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

[Mister_T]

Thank you so much @v-rzhou-msft! It works!

I was sitting at this problem for a long time, but thanks to your help I finally have a solution.

Thanks again and best regards,

Mister_T

[Vera_33]

Hi @Mister_T 

When you filter down to 4 countries, you calculate against the Grand Total of whole table or the 4 countries you select? Here is one against the whole table

Cumulative = 
VAR CurReseller = SELECTEDVALUE('Table'[Reseller ])
VAR Cur = CALCULATE([% of Total],'Table'[Reseller ]=CurReseller)
RETURN
CALCULATE([% of Total],FILTER(ALL('Table'[Reseller ]),[% of Total]>=Cur))

[Mister_T]

Hi @Vera_33,

thank you very much for your reply!

When I filter down to a specific set of countries, the percentage of each reseller should then be in relation to the total of the chosen selection of countries.