Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

How to Calculate Subtotal in a Rows

Hi Power Bi Community,

 

I'm struggling calculating the Subtotal in a rows. This problem is similar to this I have week ago. Thanks to Greg_Deckler

https://community.powerbi.com/t5/Desktop/How-to-Calculate-Total-Column-When-is-Not-Displaying-the-Total/m-p/527753#M247151

 

When I went to the paint roller and turned on the Subtotal, somehow it doesn't show me the subtotal in a row instead it displayed the Total per column and is wrong too.

 

 

I want the Total amount in a row. How can I write a formula or do I need to create a new measure?

 

Col1 = SWITCH(TRUE(),
    
MAX('Table2'[Category]) = "A",
    CALCULATE(COUNT(Table1[Percent]),FILTER(Table1,[Buck]= "25%")),
    
MAX('Table2'[Category]) = "E",
CALCULATE(SUM(Table1[ABC]),
    FILTER(Table1,Table1[Buck] = "25%"),FILTER(Table1,Table1[Rank] = "Yes")),
    
MAX('Table2'[Category]) = "D",
CALCULATE(SUM(Table1[ABC]),
    FILTER(Table1,Table1[Buck] = "25%"), FILTER(Table1,Table1[Rank] <> "Yes")),
    
MAX('Table2'[Category]) = "C",
FORMAT(DIVIDE([INF],[TotalSumABC]),"0.0%"),

MAX('Table2'[Category]) = "B",
CALCULATE(SUM(Table1[Amt]),FILTER(Table1,SEARCH("25%)",[Buck],1,0))),

MAX('Table2'[Category]) = "F",
CALCULATE([INF] - [Value]),

MAX('Table2'[Category]) = "G",
FORMAT(DIVIDE([VAR],[Value]),"0.0%"))

 

 

Need Help
Show and Tell
Tutorial Requests
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    Presuming you are using a matrix visual, you will need to create a 3rd measure that is the sum of Col1 and Col2

     

    TotalCol = [Col1] + [Col2]

    Then put TotalCol in your visual next to Col1 and Col2

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    If, instead, Col1 and Col2 are values from the data (for example year, location, etc), you can put that field in the "Columns" of the matrix and you will get the option to turn column values on or off.

     

    Hope this helps

    David

     

     

4 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    7 years ago

    You can create a measure that adds Col1 and Col2.  The only way to have the visual provide column subtotals is to have columns defined for the matrix (as opposed to just multiple measures as in your case).

     

    Hope this helps

    David

      • dedelman_clng's avatar
        dedelman_clng
        Community Champion
        7 years ago

        Presuming you are using a matrix visual, you will need to create a 3rd measure that is the sum of Col1 and Col2

         

        TotalCol = [Col1] + [Col2]

        Then put TotalCol in your visual next to Col1 and Col2

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        If, instead, Col1 and Col2 are values from the data (for example year, location, etc), you can put that field in the "Columns" of the matrix and you will get the option to turn column values on or off.

         

        Hope this helps

        David