Forum Discussion
Helper IIIIf Measure between two measures not summing properly
Hi I need help, it seems to be reacting the right way in the columns but the Column totals aren't summing correctly.
It more or less should read if one variable is zero then take the other but.
Coalesce (
[PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL]
)
Coalesce returns the first nonblank result from the supplied parameters
If you expect 0 rather than blank
Switch (
True,
[PROJECTED_SALES_Rev] > 0, [PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL] > 0, [Bud_Sales_Rev_ALL]
)
Since you as checking this per rows ina table you might have to use sumx.
Sumx(
Values( table[column]),
Switch (
True,
[PROJECTED_SALES_Rev] > 0, [PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL] > 0, [Bud_Sales_Rev_ALL]
)
)
5 Replies
- Deku
Super User
Coalesce (
[PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL]
)
Coalesce returns the first nonblank result from the supplied parameters
If you expect 0 rather than blank
Switch (
True,
[PROJECTED_SALES_Rev] > 0, [PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL] > 0, [Bud_Sales_Rev_ALL]
)
Since you as checking this per rows ina table you might have to use sumx.
Sumx(
Values( table[column]),
Switch (
True,
[PROJECTED_SALES_Rev] > 0, [PROJECTED_SALES_Rev],
[Bud_Sales_Rev_ALL] > 0, [Bud_Sales_Rev_ALL]
)
)
- Ashish_Mathur
Hi,
Assuming the field at the extreme left hand side of the table visual is products (from the Product table), write this measure
Measure = SUMX(VALUES(Product[Products]),[PROJECTED_Sales_REV_ALL])
Hope this helps.
jimrosserThanks for the ideas, I've tried just about everything the first column is for projections and are only about 3 months. I would like to use Budget amount for any that doesnt contain a projection. That's the second column totals never add up. They are the same as the projection column even though both show different amounts.
- Ashish_Mathur
Hi,
Share the download link of the PBI file. Show the problem and the expected result very clearly.
