Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Subtotal calculation in matrix visual

Hi,

I'm trying to calculate the percentage of the column and sub culumn subtotal for each sub-category in Matrix visual :

I duplicated the table and tried to calculate the value as the percent of the column total but the calculation done by the column subtotal :

 

The actual calculation is the red field divided by the blue field, the desired calculation should be the red divided by the green field - in this case -  48.51%.
The calculation should be applied for all the fields in Table 2, any ideas? 

Thanks

 

  • Fowmy's avatar
    Fowmy
    2 years ago

    Anonymous 
    You can create a measure as follows:

    Percentage of Row = 

VAR __SalesAmount = [Sales Amount]
VAR __RowSubTotal =  CALCULATE( [Sales Amount] , REMOVEFILTERS( 'Product'[Subcategory] ))
VAR __GrandSubTotal =  CALCULATE( [Sales Amount] , REMOVEFILTERS( 'Product'[Category] , 'Product'[Subcategory] ))
VAR __ResultSubTotal =  DIVIDE( __SalesAmount, __RowSubTotal)
VAR __ResultGrandTotal =  DIVIDE( __SalesAmount, __GrandSubTotal)
RETURN
   IF( ISINSCOPE( 'Product'[Subcategory] ) , __ResultSubTotal , __ResultGrandTotal)

     

     



2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 
    You can create a measure as follows:

    Percentage of Row = 

VAR __SalesAmount = [Sales Amount]
VAR __RowSubTotal =  CALCULATE( [Sales Amount] , REMOVEFILTERS( 'Product'[Subcategory] ))
VAR __GrandSubTotal =  CALCULATE( [Sales Amount] , REMOVEFILTERS( 'Product'[Category] , 'Product'[Subcategory] ))
VAR __ResultSubTotal =  DIVIDE( __SalesAmount, __RowSubTotal)
VAR __ResultGrandTotal =  DIVIDE( __SalesAmount, __GrandSubTotal)
RETURN
   IF( ISINSCOPE( 'Product'[Subcategory] ) , __ResultSubTotal , __ResultGrandTotal)

     

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Fowmy - Works great, many thanks!