If Measure between two measures not summing properly

[jimrosser]

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.

PROJECTED_Sales_REV_ALL =

IF(

    'Table1_1'[PROJECTED_SALES_Rev] <= 0,

   'Table1_1'[Bud_Sales_Rev_ALL], If('Table1_1'[PROJECTED_SALES_Rev] > 0,Table1_1[PROJECTED_SALES_Rev],0)

)

[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.

[jimrosser]

Thanks 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.

[Anonymous]

Hi, @jimrosser 

Have you solved your problem yet? If not, can you provide example files and what output you expect?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the problem and the expected result very clearly.

[Deku · Accepted solution]

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]

)

)