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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dhanekula1996
Helper I
Helper I

matrix Removing Row totals at higher hierarchy and displaying Grand total

Hello All,
I am trying to customize a matrix visualization. this is my excpected Out put how can i achieve this ( Pictures atached).
Please let me know If you need more info.


g1.PNG

 

g2.PNG

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @dhanekula1996 ,

 

Please try:

Measure = IF(ISINSCOPE('Table'[Item]) ,SUM('Table'[cost]), IF(ISINSCOPE('Table'[Sample]),BLANK(),SUM('Table'[cost])))

Output:

Eyelyn9_0-1638955542338.png

 

Best Regards,
Eyelyn Qin
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

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @dhanekula1996 ,

 

Please try:

Measure = IF(ISINSCOPE('Table'[Item]) ,SUM('Table'[cost]), IF(ISINSCOPE('Table'[Sample]),BLANK(),SUM('Table'[cost])))

Output:

Eyelyn9_0-1638955542338.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ValtteriN
Super User
Super User

Hi,

This can be achieved by using ISFILTERED combined with IF. 

My example data:

ValtteriN_0-1638725986796.png


The DAX I used:

ValtteriN_1-1638726033262.png

Matrix with Blanks on lvl 1 = IF(
ISFILTERED('Matrix example'[Lvl 1])&&ISFILTERED('Matrix example'[Lvl 2])=FALSE(),"", //This checks if the current row is level 1 if so -> returns blank else returns the calculation below
SUM('Matrix example'[Value])) //Sum can be replaced with measure

End result:

ValtteriN_2-1638726078075.png

 




Hopefully this helps and if it does consider accepting this as a solution!

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello,
Thank you for your solution. WHen I have tried this I was able to show Blank values at Level 1 But my Row total per row (Left to right) is also showing as Blanck. I need to show value of the total in there as well. Like using SUMX ??? 

Hi,

To solve this you can create separate total measure which doesn't have the ISFILTERED structure. So for example something like:

 [Total cost]=sum([cost])+sum([Max cost]). Then If you place this Into The Matrix the value will show. 

Example:

IMG_20211206_172934.jpg





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@dhanekula1996 , I doubt this is possible with standard visual,

 As a work around. You need to append data sample distinct values in the item table and the use option Show Item with no data.

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.