Help with Grand total for matrix visual.

[mgb-nav-pbi]

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

[mgb-nav-pbi · Accepted solution]

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 · Accepted solution]

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.

[mgb-nav-pbi]

Hi @v-yalanwu-msft , your solution help me find a work around. Thanks a lot for your help. Appreciate it 🙂

[mgb-nav-pbi · Accepted solution]

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 🙂

[lbendlin]

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.