cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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"

 

Nick_Singh_0-1600437812115.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




View solution in original post

Highlighted

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

20.png

Regards,

Xiaoxin Sheng

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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




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

Proud to be a Super User!




View solution in original post

Highlighted
Solution Sage
Solution Sage

@Nick_Singh 

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

 

2020-09-18_224146.png2020-09-18_224059.png2020-09-18_224035.png

Highlighted

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

Highlighted
Community Support
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.
Highlighted

Hi @v-shex-msft 

 

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-20Aug-20MoM Change CT MRI Ultrasound X-Ray Other
 Catherine Eady CTJul-20129  Catherine Eady620370(250)24%23%16%11%26%
 Catherine Eady MRIJul-20123  Minerva Hultgren61980318412%0%57%18%13%
 Catherine Eady UltrasoundJul-20157  Chance Evens763(763)15%23%18%23%21%
 Catherine Eady X-RayJul-20109  John Smith415(415)44%25%30%0%0%
 Catherine Eady OtherJul-20102  Mollie Stever542(542)23%0%25%31%22%
 Minerva Hultgren CTJul-20168          
 Minerva Hultgren UltrasoundJul-20128          
 Minerva Hultgren X-RayJul-20142          
 Minerva Hultgren OtherJul-20181          
 Chance Evens CTJul-20114          
 Chance Evens MRIJul-20176          
 Chance Evens UltrasoundJul-20137          
 Chance Evens X-RayJul-20172          
 Chance Evens OtherJul-20164          
 John Smith CTJul-20184          
 John Smith MRIJul-20105          
 John Smith UltrasoundJul-20126          
 Mollie Stever CTJul-20124          
 Mollie Stever UltrasoundJul-20134          
 Mollie Stever X-RayJul-20166          
 Mollie Stever OtherJul-20118          
 Minerva Hultgren UltrasoundAug-20683          
 Minerva Hultgren X-RayAug-20120          
 Catherine Eady OtherAug-20160          
 Catherine Eady CTAug-20108          
 Catherine Eady MRIAug-20102          
Highlighted

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

20.png

Regards,

Xiaoxin Sheng

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors