Measure as a row in matrix

[LoryMenCR]

Dear All,

i have the following matrix visual

Group	Value
Group Description A	5000
Group Description B	- 2000
Group Description C	- 5000
Group Description D	+3000
Total	1000

I have created a measure which basically do a calculation on groups.

For example:

Measure1= Group Description A - Group Description B.

Now I need to insert this measure as a row in the matrix, as follows:

Group	Value
Group Description A	5000
Group Description B	- 2000
Measure1	3000
Group Description C	- 5000
Group Description D	+3000
Total	1000

I am not able to do this.

If I insert my measure in the field value of the matrix, this is what I get:

Group	Value	Measure1
Group Description A	5000	5000
Group Description B	- 2000	-2000
Group Description C	- 5000	0
Group Description D	+3000	0
Total	1000	3000

Do you have a solution for this case?
Thank you a lot

[Kedar_Pande · Accepted solution]

@LoryMenCR 

Create a Supporting Table for Groups:

GroupTable = 
DATATABLE(
"Group", STRING,
{
{"Group Description A"},
{"Group Description B"},
{"Measure1"},
{"Group Description C"},
{"Group Description D"}
}
)

Create the Measure for Measure1:

Measure1 = 
VAR GroupA_Value = CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description A")
VAR GroupB_Value = CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description B")
RETURN GroupA_Value - GroupB_Value

Add a Dynamic Measure for the Value Column:

DynamicValue = 
SWITCH(
TRUE(),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description A", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description A"),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description B", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description B"),
SELECTEDVALUE('GroupTable'[Group]) = "Measure1", [Measure1],
SELECTEDVALUE('GroupTable'[Group]) = "Group Description C", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description C"),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description D", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description D"),
BLANK()
)

Add GroupTable[Group] to the Rows field in the matrix visual.
Add the DynamicValue measure to the Values field in the matrix.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous · Accepted solution]

Hi @LoryMenCR , hello Kedar_Pande, thank you for your prompt reply!

I agree with Kedar_Pande.

Additionally, you might consider adding a sort order column to the supporting table.

This will help control the display order of the rows in the matrix.

GroupsTable = DATATABLE(
    "Group", STRING,
    "SortOrder", INTEGER,
    {
        {"Group Description A", 1},
        {"Group Description B", 2},
        {"Measure1", 3},
        {"Group Description C", 4},
        {"Group Description D", 5}
    }
)

Next, sort the group column using the sort order as illustrated below:

Then, utilize the measure mentioned by Kedar Pande for your reference:

Based on my test, since the group description for value b is -2000, we need to apply the ABS function to ensure it uses the correct value.

Measure1 = 
VAR GroupA_Value = CALCULATE(SUM('Table'[Value]), 'Table'[Group] = "Group Description A")
VAR GroupB_Value = CALCULATE(SUM('Table'[Value]), 'Table'[Group] = "Group Description B")
RETURN GroupA_Value - ABS(GroupB_Value)

Best regards,

Joyce

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

[Kedar_Pande · Accepted solution]

@LoryMenCR 

Create a Supporting Table for Groups:

GroupTable = 
DATATABLE(
"Group", STRING,
{
{"Group Description A"},
{"Group Description B"},
{"Measure1"},
{"Group Description C"},
{"Group Description D"}
}
)

Create the Measure for Measure1:

Measure1 = 
VAR GroupA_Value = CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description A")
VAR GroupB_Value = CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description B")
RETURN GroupA_Value - GroupB_Value

Add a Dynamic Measure for the Value Column:

DynamicValue = 
SWITCH(
TRUE(),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description A", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description A"),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description B", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description B"),
SELECTEDVALUE('GroupTable'[Group]) = "Measure1", [Measure1],
SELECTEDVALUE('GroupTable'[Group]) = "Group Description C", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description C"),
SELECTEDVALUE('GroupTable'[Group]) = "Group Description D", CALCULATE(SUM('MainTable'[Value]), 'MainTable'[Group] = "Group Description D"),
BLANK()
)

Add GroupTable[Group] to the Rows field in the matrix visual.
Add the DynamicValue measure to the Values field in the matrix.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn