IF statement calculating both values

[Adams_Apple]

I am trying to use an IF statement to use values from two different tables. The first table contains actual production data and the second contains projections for future numbers. This is used in a matrix to display actual data for the past and current month and then projection data for future months in the year.

It seems to be using values from both tables in months where data exists in both tables during the same month. 

Projected_BinsPacked =

CALCULATE(

SUMX(

    VALUES('Date'[Date].[Month]),

    IF(SUM('Bins'[Bins Packed]) > 0,

        SUM('Bins'[Bins Packed]),

        MAX(Projections[Bins Packed])

        )

    ))

thank you

[Anonymous]

Hi @Adams_Apple ,

You can create another new measure as below and put it on Values fields of matrix to replace the measure [Projected_BinsPacked]:

Measure = SUMX ( VALUES ( 'Date'[Date].[Month] ), [Projected_BinsPacked] )

If the above one can't help you figure out, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic base on the provided sample data. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[suparnababu8]

Hi @Adams_Apple 

It seems like  your current approach likely uses an IF statement that checks for the existence of actual data. If actual data exists, the measure should use it. Otherwise, it should use projected data.  you can try with COLEASE dax function in place of IF. Let's give a try with below measure

Projected_BinsPacked =
CALCULATE(
    SUMX(
        VALUES('Date'[Date].[Month]),
        COALESCE(SUM('Bins'[Bins Packed]), MAX(Projections[Bins Packed])))
)

Let me know if it works.

Thanks

[Adams_Apple]

that works great, thank you. however, the sum total isn't correct. how can i modify that measure so it has the correct sum total?

[suparnababu8 · Accepted solution]

Hi @Adams_Apple 

Th's good to hear the measure is working for you! In order to works sum of total correct, you may use SUMX function to iterate over the table and calculate total sum correctly. Give a try with the below measure.

Projected_BinsPacked =
SUMX(
    VALUES('Date'[Date].[Month]),
    CALCULATE(
        COALESCE(SUM('Bins'[Bins Packed]), MAX(Projections[Bins Packed])))
)

Let me know if it works.

Thank you!

[Adams_Apple]

Thank you so much, works perfectly!