topic Re: Column total and HASONEVALUE in DAX Commands and Tips

Column total and HASONEVALUE

Anonymous — Mon, 10 Oct 2022 13:48:06 GMT

Hi,

I have this measure that is calculating the row total (per counterparty) based on another measure [P/L].

However, the column total is wrong so how can I include the date dimension to the measure in order for the column total to add up correctly? (-5.748 - 5.031 - 3.168 - 38 -37 -38) = -14.660). Can also the date be included in the HASONEVALUE -function in some way?

Re: Column total and HASONEVALUE

amitchandak — Mon, 10 Oct 2022 16:18:40 GMT

Anonymous , remove if and hasonevalue code and just try sumx(Value(.....) code

Re: Column total and HASONEVALUE

Anonymous — Mon, 10 Oct 2022 17:04:06 GMT

Hi amitchandak ,

Apparently the HASONEVALUE was not needed, the row subtotals are correct with it removed, but the column subtotal is still wrong i.e I get the same result.

Re: Column total and HASONEVALUE

Anonymous — Tue, 11 Oct 2022 06:20:38 GMT

Hi Anonymous ,

As far as I know, column subtotal is based on the column in Matrix column field (MonthName). So here I suggest you to add the whole table in SUMX().

Measure = SUMX(TransactionDaily,[P/L])

My Sample:

P/L = MAX(TransactionDaily[Value])

If I use the measure as yours above, I will get same error in column subtotal. If I use mine, it works. Result is as below.

Best Regards,
Rico Zhou

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

Re: Column total and HASONEVALUE

Anonymous — Tue, 11 Oct 2022 12:58:14 GMT

Hi Anonymous , thanks for your input.

The measure [P/L] is calculated using values from two different tables.

As in this simplified example, volumes from one table and prices from another table.

Measure for the total

P/L and P/L Total shows both wrong totals.

Re: Column total and HASONEVALUE

Anonymous — Tue, 11 Oct 2022 17:20:54 GMT

Apparently just adding the whole table in SUMX works in my simplified example,

but when doing it in the real case I just receive a blank matrix with no values.