Forum Discussion

hidenseek9's avatar
hidenseek9
Post 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

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

Hey,

 

You can create disconnected table

FCF Categories =
DATATABLE(
"Category", STRING,
{
{"Previous Year (PY)"},
{"Target (TY)"},
{"Forecast (FC)"}
}
).

then you can assign value dynamicaaly using SELECTEDVALUE dax function

 

Free Cash Flow Measure =
SWITCH(
SELECTEDVALUE(FCF Categories[Category]),
"Previous Year (PY)", [PY Measure],
"Target (TY)", [TY Measure],
"Forecast (FC)", [FC Measure]
)

 

if it doesnt work , please share sample pbix

 

Thanks

  • hidenseek9's avatar
    hidenseek9
    Post Patron
    1 year ago

    Thank you both for the suggestion. 

    pankajnamekar25 rajendraongole1 

     

    Are you not able to access my sample data?

    Sample data 

     

    As a test, I have created measures for forecast values for some category items. 

     

    Much appreciate your support!