Customized Matrix & SUM function & Customized Subtotals

[Anonymous]

Hi All, 

This time I have got stucked with customised matrix. 

The goal is to to have matrix with custom subtotal  A+C = 8

________________________________________________________________________________________

input table with values 

category table 

model Category[Category]1-*Input[Letter]

functions:

A

B

C

A+C

In case I try to represent values on cards visualizatins I got folowing result

In case I try to represent values in matrix table I have difficulties to present A+C function 

Thank you in advance for your reply. 

[Markus_Re]

Hi @Anonymous,

in your "Values for Matrix"-Measure you are using the Column "Input[Letter ]", and try to select "A+C",

As you state above this table does not include "A+C", only the "Category"-Table does.

So i would suggest swapping out SELECTEDVALUE(Input[Letter ]) for SELECTEDVALUE(Category[Category]) in the Values for Matrix"-Measure

Hope this helps.

Best regards,

Markus

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

[Anonymous]

Thank you for your reply, 

unfortunately it doesn't work 😞

[Markus_Re · Accepted solution]

Hi @Anonymous,

could you try this for A+C:

A+C = SUMX(FILTER(ALLSELECTED(Input), Input[Letter] = "A" || Input[Letter] = "C"), Input[Value])

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

[Anonymous]

@Markus_Re 
It works great! 

Could you advice me how to approach next issue
I would like to have this kind of matrix 

where A+C is the sum of both A and C but considering the category One, Two, .... 
Currently there are no other values so the results will be
A+C | 2 4 6 0 

[Markus_Re]

Hi @Anonymous,

Here is how I would solve this:
Create a third table that includes Value and discription (just like the table "Category), but this table has no active relation to any other table (in my case called ValueDescription). The tables can´t be related because of the fact, that for the Category A+C no values exist in the Input-Table and so the Measure A+C in connection with the new table will never return any values:

To build the visual I used this columns: Category[Category] in Rows, ValueDescription[Description] in Columns, and [Values for Matrix] in the Values-Section.

For this to work all the Measures have to be changed like the following:

For example A (B,C):

A =

IF(HASONEVALUE(ValueDescription[Description]),
SUMX(FILTER(ALLSELECTED(Input), Input[Letter] = "A" && Input[Description] = SELECTEDVALUE(ValueDescription[Description])), Input[Value]),
SUMX(FILTER(ALLSELECTED(Input), Input[Letter] = "A"), Input[Value]))

and A+C:

A+C =

IF(HASONEVALUE(ValueDescription[Description]),
SUMX(FILTER(ALLSELECTED(Input), (Input[Letter] = "A" || Input[Letter] = "C") && Input[Description] = SELECTEDVALUE(ValueDescription[Description])), Input[Value]),
SUMX(FILTER(ALLSELECTED(Input), Input[Letter] = "A" || Input[Letter] = "C"), Input[Value]))
This change is necessary because as described above, we can´t enable a relation between Input and ValueDescription. All it does is, it checks if a single ValueDescription is Selected and if so it returns only the Sum of the Selected Description