cancel
Showing results for
Did you mean:
Frequent Visitor

## Matrix Visual View

Hi Guys

Need some help please. Below are 2 matrix visuals, just wondering how can we show all the info in one table visual? Basically, I have amounts on a timeline for different type of services (CT, MRI, Other, US and X-Ray). Not interested in showing \$ associated with these services at individual level, but the % split of "Total"

2 ACCEPTED SOLUTIONS
Super User IV

@Nick_Singh Well, one way would be to use this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

Would need source data to play with to be more specific. @ me in replies. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Community Support

Hi @Nick_Singh,

In fact, you can't direct achieve based on raw table structure and matrix feature. For your requirement, I think you need to create a table with a specific category that includes month and service value and write measure formula to calculate.

Table:

``````Unioned =
UNION (
'Table',
SUMMARIZE (
'Table',
'Table'[Name],
[Month],
"Service", [Month],
"Amount", SUM ( 'Table'[Amount] )
)
)
``````

Measure:

``````Measure =
VAR currService =
SELECTEDVALUE ( 'Unioned'[Service] )
VAR currMonth =
SELECTEDVALUE ( 'Unioned'[Month] )
RETURN
IF (
currService <> currMonth & "",
DIVIDE (
CALCULATE (
SUM ( 'Unioned'[Amount] ),
FILTER ( ALLSELECTED ( 'Unioned' ), [Service] <> [Month] & "" ),
VALUES ( 'Unioned'[Name] ),
VALUES ( Unioned[Service] )
),
CALCULATE (
SUM ( 'Unioned'[Amount] ),
FILTER ( ALLSELECTED ( 'Unioned' ), [Service] <> [Month] & "" ),
VALUES ( 'Unioned'[Name] )
),
0
),
SUM ( 'Unioned'[Amount] )
)
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
6 REPLIES 6
Community Support

Hi @Nick_Singh,

I think you can write a formula to calculate the percentage of the current row and use it on the value field.

``````Measure =
DIVIDE (
CALCULATE (
SUM ( Table[Value] ),
ALLSELECTED ( Table ),
VALUES ( Table[Ref_Suburb] ),
VALUES ( Table[services] )
),
CALCULATE (
SUM ( Table[Value] ),
ALLSELECTED ( Table ),
VALUES ( Table[Ref_Suburb] )
),
-1
)
``````

If the above formula not helps, can you please share some dummy data to test?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Thank you for helping with this measure, however it didnt get the expected result. Basically, I have a list of customers and the type of service they utilise every month. Whilst I am trying to figure what is the total value of services utilised by each customer month on month, I wanted to get a holistic view of the type of services they utilise (so don't really need this on a month on month basis, but on the overall value) See below set of sample inputs and expected outputs:

Kind regards

Nick

 Inputs Expected Output Name Service Month Amount Name Jul-20 Aug-20 MoM Change CT MRI Ultrasound X-Ray Other Catherine Eady CT Jul-20 129 Catherine Eady 620 370 (250) 24% 23% 16% 11% 26% Catherine Eady MRI Jul-20 123 Minerva Hultgren 619 803 184 12% 0% 57% 18% 13% Catherine Eady Ultrasound Jul-20 157 Chance Evens 763 - (763) 15% 23% 18% 23% 21% Catherine Eady X-Ray Jul-20 109 John Smith 415 - (415) 44% 25% 30% 0% 0% Catherine Eady Other Jul-20 102 Mollie Stever 542 - (542) 23% 0% 25% 31% 22% Minerva Hultgren CT Jul-20 168 Minerva Hultgren Ultrasound Jul-20 128 Minerva Hultgren X-Ray Jul-20 142 Minerva Hultgren Other Jul-20 181 Chance Evens CT Jul-20 114 Chance Evens MRI Jul-20 176 Chance Evens Ultrasound Jul-20 137 Chance Evens X-Ray Jul-20 172 Chance Evens Other Jul-20 164 John Smith CT Jul-20 184 John Smith MRI Jul-20 105 John Smith Ultrasound Jul-20 126 Mollie Stever CT Jul-20 124 Mollie Stever Ultrasound Jul-20 134 Mollie Stever X-Ray Jul-20 166 Mollie Stever Other Jul-20 118 Minerva Hultgren Ultrasound Aug-20 683 Minerva Hultgren X-Ray Aug-20 120 Catherine Eady Other Aug-20 160 Catherine Eady CT Aug-20 108 Catherine Eady MRI Aug-20 102
Community Support

Hi @Nick_Singh,

In fact, you can't direct achieve based on raw table structure and matrix feature. For your requirement, I think you need to create a table with a specific category that includes month and service value and write measure formula to calculate.

Table:

``````Unioned =
UNION (
'Table',
SUMMARIZE (
'Table',
'Table'[Name],
[Month],
"Service", [Month],
"Amount", SUM ( 'Table'[Amount] )
)
)
``````

Measure:

``````Measure =
VAR currService =
SELECTEDVALUE ( 'Unioned'[Service] )
VAR currMonth =
SELECTEDVALUE ( 'Unioned'[Month] )
RETURN
IF (
currService <> currMonth & "",
DIVIDE (
CALCULATE (
SUM ( 'Unioned'[Amount] ),
FILTER ( ALLSELECTED ( 'Unioned' ), [Service] <> [Month] & "" ),
VALUES ( 'Unioned'[Name] ),
VALUES ( Unioned[Service] )
),
CALCULATE (
SUM ( 'Unioned'[Amount] ),
FILTER ( ALLSELECTED ( 'Unioned' ), [Service] <> [Month] & "" ),
VALUES ( 'Unioned'[Name] )
),
0
),
SUM ( 'Unioned'[Amount] )
)
``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User I

In the Matrix (First one with Values) , in the Value Fields expand the Values and select Show Values as and select Percentage to the Grand total... or any other/ as shown below

Proud to be a Super User!

Super User IV

@Nick_Singh Well, one way would be to use this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

Would need source data to play with to be more specific. @ me in replies. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

Thanks @Greg_Deckler , firsttly apologies for not using the best approach to posting my question - rookie mistake! What you suggested makes absolute sense.

Secondy, I have looked at your custom matrix hierarchy solution (the new hotness). It addresses pretty much what I need; however as you pointed out, its somewhat complex. I will try playing around and see if I can replicate for my problem.

Thank you once again.

Kind regards

Nick

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!