topic Measure Subtotals in Matrix not working for both Rows and Columns in DAX Commands and Tips

Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Thu, 20 Feb 2020 17:11:36 GMT

hello,

I added this Measure in a Matrix, which calculates the transactions volume by hour:

Total Transactions = AVERAGE(table1[percentage_by_hour]) * sum(table2[transaction_value])

"table1" has the percentage distribution of transactions by hour, as shown below:

hour % channel

1 1% Channel A

2 2% Channel B

3 8% Channel A

... .... ....

"table2" has the total volume of transactions for the day, by Channel:

day channel transaction_volume

feb 1 Channel A 100

feb 2 Channel B 40

... ....

The Matrix has 'Hours' in the columns and 'Channels' in the rows and for some reasons only either the rows subtotal or columns subtotal is showing correctly, but not both together. This is the Measure that I used in the Matrix to show results:

Total Transactions NEW =

VAR monthlyAverages = AVERAGEX(VALUES(table2[Channel]),[Total Transactions])

VAR AverageTotal = summarize(table2,table2[Channel],"Monthly AVG",monthlyAverages)

RETURN

if(HASONEVALUE(table2[Channel]),

monthlyAverages,

sumx(AverageTotal,[Monthly AVG]))

This is working by 'Channel', therefore the rows subtotal is correct, but the columns subtotal is wrong.

If I replace 'Channel' with 'Hour' the opposite will happen (columns subtotal is correct, and rows subtotal is wrong).

How can I make it work so both rows subtotal and columns subtotal show correctly?

thanks!

Re: Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Sat, 22 Feb 2020 17:58:02 GMT

You don't understand how variables in DAX work. Please try to do some reading on this. A hint is this: a variable is calculated only once at the point of its definition.

Re: Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Sun, 23 Feb 2020 10:18:25 GMT

hi Anonymous ,

The Total calculation does precisely what you asked it to do, namely calculate the average for the day.

What you actually want to see, if I am not mistaken, is the sum of the hourly averages. In order to calculate this you need to calculate the hourly values again and then sum them into the required total.

Something like (depends also in the relations present):

SUMX(
summarize('table2',
table2[Channel],
table1[Hour],
"Monthly AVG",monthlyAverages
),
[Monthly AVG])

replace sumx(AverageTotal,[Monthly AVG]) with the above code snippet

Jan

if this is a solution for you, don't forget to mark it as such. thanks

Re: Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Sun, 23 Feb 2020 14:24:46 GMT

Hi there.

I'm not sure why you're using AVERAGE... I think this is not what you should do. Have a look at this model and the calculation. I think this might be what you really want...

https://1drv.ms/x/s!ApyQEauTSLtOgYJNmDbSzQj8jbcu-A?e=WgQjNU

Best

Re: Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Mon, 24 Feb 2020 15:44:45 GMT

thanks Anonymous , but unfortunately this won't work as I cannot have fields from different tables in the Summarize function.

Anonymous...

Re: Measure Subtotals in Matrix not working for both Rows and Columns

Anonymous — Mon, 24 Feb 2020 15:53:37 GMT

Anonymous, please download the Excel file and open the PowerPivot model. You'll then see how it's structured and the measure you want.

Best
D