Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
1 year ago
Solved

Creating relationship between measures and a table

Hello    I would like to forecast current year free cash flow and compare vs previous year as well as this year's target in a table view and a waterfall (bridge) view.    I have a simple data se...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi hidenseek9,

     

    The issue occurs because SELECTCOLUMNS() is causing duplicate entries, instead use SUMMARIZE() so that each category appears only once per year and removes duplicates.

     

    Replace the measure with below one:

    FCF_Forecast_Table =
    VAR FCF_2024 = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2024 Actual", "Value", [FCF_2024])

    VAR FCF_Target = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2025 Target", "Value", [FCF_2025_Target])

    VAR FCF_Forecast = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2025 Forecast", "Value", [FCF_2025_Forecast])

    RETURN UNION(FCF_2024, FCF_Target, FCF_Forecast)

     

    This should now correctly display only one row per category per year, removing duplicates and you can use this structured table in a Matrix or Waterfall Chart without any repetition issues.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
1 year ago

Thank you pankajnamekar25 rajendraongole1 

 

By using SELECTCOLUMNS on both 2025 target and 2024 actual, I was able to stack a table such as below. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

From here, I would need to attach the forecast value in the same format after 2024 actual. However, by using SELECTCOLUMNS it returns 22 of the same data such as below. 

How can I return a clean table with 

1 line for each item?

 

 

 

 

 

 

v-achippa's avatar
v-achippa
Icon for Community Support rankCommunity Support
1 year ago

Hi hidenseek9,

 

The issue occurs because SELECTCOLUMNS() is causing duplicate entries, instead use SUMMARIZE() so that each category appears only once per year and removes duplicates.

 

Replace the measure with below one:

FCF_Forecast_Table =
VAR FCF_2024 = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2024 Actual", "Value", [FCF_2024])

VAR FCF_Target = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2025 Target", "Value", [FCF_2025_Target])

VAR FCF_Forecast = SUMMARIZE('FCF', 'FCF'[Category], "Year", "2025 Forecast", "Value", [FCF_2025_Forecast])

RETURN UNION(FCF_2024, FCF_Target, FCF_Forecast)

 

This should now correctly display only one row per category per year, removing duplicates and you can use this structured table in a Matrix or Waterfall Chart without any repetition issues.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

  • hidenseek9's avatar
    hidenseek9
    Icon for Post Patron rankPost Patron
    1 year ago

    v-achippa 

    Thank you for the reply. 

    Summarize did not work as described, so I used ROW function to create a table, since it was a short table. 

     

    Many thanks

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi hidenseek9,

       

      Thank you for the response, did this resolved your issue? if yes please accept your own post as a solution so that other community members will find it more quickly.

       

      Thanks and regards,

      Anjan Kumar Chippa