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 pe...
  • 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)

     

     



Fowmy's avatar
Fowmy
Icon for Super User rankSuper 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!