Column total and HASONEVALUE

[Anonymous]

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? 

[amitchandak]

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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.