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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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

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

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Hi @Anonymous,

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
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

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.
Anonymous
Not applicable

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          

Hi @Anonymous,

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.
VijayP
Super User
Super User

@Anonymous 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Greg_Deckler
Super User
Super User

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

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

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.