topic Customized Matrix & SUM function & Customized Subtotals in DAX Commands and Tips

Customized Matrix & SUM function & Customized Subtotals

Anonymous — Mon, 29 Mar 2021 17:00:04 GMT

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

Re: Customized Matrix & SUM function & Customized Subtotals

Markus_Re — Mon, 29 Mar 2021 17:09:22 GMT

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!

Re: Customized Matrix & SUM function & Customized Subtotals

Anonymous — Mon, 29 Mar 2021 17:43:37 GMT

Thank you for your reply,

unfortunately it doesn't work 😞

Re: Customized Matrix & SUM function & Customized Subtotals

Markus_Re — Mon, 29 Mar 2021 17:56:58 GMT

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!

Re: Customized Matrix & SUM function & Customized Subtotals

Anonymous — Mon, 29 Mar 2021 18:13:01 GMT

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

Re: Customized Matrix & SUM function & Customized Subtotals

Markus_Re — Mon, 29 Mar 2021 19:04:12 GMT

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