Forum Discussion

mgb-nav-pbi's avatar
mgb-nav-pbi
Advocate I
4 years ago
Solved

Help with Grand total for matrix visual.

Hi all,

 

My mind is boggling now.

 

In a matrix with two categories in Row headers, is it possible to show the grand total for both levels of categories? I have a first row with the No of Customers, and a second row with Revenue. I'd like to show the total for both the measures in the grand total like I can do in Excel ( Please see the image below, showing only the result for one quarter).

 

I thought of HASONEVALUE, but I believe it is not helping me for different categories needed to be added separately I am stuck with the following (check image)

 

Is there a way to achieve this in Power BI?


Thank you in advance.

mgb-nav-pbi

Need Help
Tips and Tricks
  • mgb-nav-pbi's avatar
    mgb-nav-pbi
    4 years ago

    Hi lbendlin 

    Thank you for your reply. I am trying to fix my grand total for my matrix visual. Clearly my grand total is not correct as shown below

    What I am trying is to find a way to show the total for each category (i.e Total for Revenue and Total for No of Customers) in the grand total (if possible, IDK 😞 ) like shown in image below.

    I am not being able to attach a sample pbix file here.
    TIA 🙂

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, mgb-nav-pbi ;

    You could add a blank column add rows,then create a measure.

    1.create a blank column.

    Column =

    2.add the matrix rows.

    3. then create a measure.

    Measure = 
IF(ISINSCOPE('financials'[Column]),
   IF(ISINSCOPE('financials'[Country]),
    SUM(financials[Sale Price]),
      CALCULATE(SUM(financials[Sale Price]),FILTER('financials',[Product]="Amarilla"))),
         CALCULATE(SUM(financials[Sale Price]),FILTER('financials',[Product]="Carretera")))

    The final output is shown below:

    Or

     

    If it not solve your problem,you could copy table in your reply.
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    A measure in a matrix visual needs to consider four (!) different areas:

    - individual cells

    - column totals

    - row totals

    - grand total

     

    Each of these can have different logic, including the one you want.  (Note: Oftentimes solving for the Grand Total will also solve for the other areas).

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.

  • mgb-nav-pbi's avatar
    mgb-nav-pbi
    Advocate I
    4 years ago

    Hi lbendlin 

    Thank you for your reply. I am trying to fix my grand total for my matrix visual. Clearly my grand total is not correct as shown below

    What I am trying is to find a way to show the total for each category (i.e Total for Revenue and Total for No of Customers) in the grand total (if possible, IDK 😞 ) like shown in image below.

    I am not being able to attach a sample pbix file here.
    TIA 🙂

     

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, mgb-nav-pbi ;

    You could add a blank column add rows,then create a measure.

    1.create a blank column.

    Column =

    2.add the matrix rows.

    3. then create a measure.

    Measure = 
IF(ISINSCOPE('financials'[Column]),
   IF(ISINSCOPE('financials'[Country]),
    SUM(financials[Sale Price]),
      CALCULATE(SUM(financials[Sale Price]),FILTER('financials',[Product]="Amarilla"))),
         CALCULATE(SUM(financials[Sale Price]),FILTER('financials',[Product]="Carretera")))

    The final output is shown below:

    Or

     

    If it not solve your problem,you could copy table in your reply.
    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.