Forum Discussion

rohit08's avatar
rohit08
Regular Visitor
3 years ago
Solved

Interesting Dax Challenge

Hi Power BI Community

 

Happy New Year!

 

I need some suggestions on how to put DAX calculated measure when we have Actual at Date Level and then Forecast (without date) been appended in same table. The table name is Sales 

 

Below is the table structure showing sample records

 

 

 

We need to create below view in Power BI where aggregated forecast value (by usinig combination of Country and Name) and shown next to rows for Actual data. It is expected that aggregated forecast value will repeat for every date. 

 

Please see below screenshot showing required view. Any code suggestions here for DAX to create measure for Forecast column here please?

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Actual value measure: = 
SUM( Data[Actual Value] )

     

    Forecast measure: =
IF (
    NOT ISBLANK ( [Actual value measure:] ) && HASONEVALUE ( Data[City] ),
    CALCULATE ( SUM ( Data[Forecast] ), REMOVEFILTERS ( Data[Date] ) )
)

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Actual value measure: = 
SUM( Data[Actual Value] )

     

    Forecast measure: =
IF (
    NOT ISBLANK ( [Actual value measure:] ) && HASONEVALUE ( Data[City] ),
    CALCULATE ( SUM ( Data[Forecast] ), REMOVEFILTERS ( Data[Date] ) )
)