Matrix Visual View

[Anonymous]

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"

[Greg_Deckler · Accepted solution]

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

[v-shex-msft]

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

[Anonymous]

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

[v-shex-msft · Accepted solution]

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

Regards,

Xiaoxin Sheng

[VijayP]

@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

[Greg_Deckler · Accepted solution]

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

[Anonymous]

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