Measure Subtotals in Matrix not working for both Rows and Columns

[Anonymous]

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!

[Anonymous]

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

D

[JustJan]

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

[Anonymous]

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

@Anonymous...

[Anonymous]

@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

[Anonymous]

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.