Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
mgb-nav-pbi
Advocate I
Advocate I

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

Required.png

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)

NotRequired.png

Is there a way to achieve this in Power BI?


Thank you in advance.

mgb-nav-pbi

2 ACCEPTED SOLUTIONS
mgb-nav-pbi
Advocate I
Advocate I

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

IncorrectGrandtotal.png

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.

Required.png

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

 

View solution in original post

v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1644815890432.png

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:

vyalanwumsft_1-1644815936789.png

Or

vyalanwumsft_2-1644816068584.png

 

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1644815890432.png

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:

vyalanwumsft_1-1644815936789.png

Or

vyalanwumsft_2-1644816068584.png

 

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.

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

mgb-nav-pbi
Advocate I
Advocate I

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

IncorrectGrandtotal.png

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.

Required.png

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

 

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!