Calculate % of Total and Cumulative %

[NidhiBhusari]

Hello All,

I want to calculate % of Total & Cumulative % for the fields in the matrix. As we drill down or drill up i.e from Category --> SubCategory or vice versa the values should be shown accordingly. It will be really helpful if someone can suggest the logic for the same. I am using a sample superstore dataset.

Regards,
Nidhi

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

I hope the below can provide some ideas on creating a solution for your data model.

Sales measure: = 
SUM( Sales[Sales] )

Sales cumulate percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER (
            ALL ( Category ),
            Category[Category] = MAX ( Category[Category] )
                && [Sales measure:] >= _sales
        )
    )
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categorycumulatesales =
    CALCULATE (
        [Sales measure:],
        FILTER ( ALL ( Category[Category] ), [Sales measure:] >= _subcategoryallsales )
    )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _subcategorycumulatesales, _subcategoryallsales ),
        DIVIDE ( _categorycumulatesales, _categoryallsales )
    )

Sales percentage: = 
VAR _sales = [Sales measure:]
VAR _subcategoryallsales =
    CALCULATE ( [Sales measure:], ALLEXCEPT ( Category, Category[Category] ) )
VAR _categoryallsales =
    CALCULATE ( [Sales measure:], ALL ( Category ) )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Category[Subcategory] ), DIVIDE ( _sales, _subcategoryallsales ),
        DIVIDE ( _subcategoryallsales, _categoryallsales )
    )

[amitchandak]

@NidhiBhusari , based on what I got example

 Cumm Sales % = divide( CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date]))), CALCULATE(SUM(Sales[Sales Amount]) , all()) )

or

Cumm Sales % = divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date]))), CALCULATE(SUM(Sales[Sales Amount]) , allselected()) )