Forum Discussion
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 set on previous year and this years target and in BI shown in a matrix view.
Problems:
1. I calculated this years forecast as measures hence they do not show in the right place in the matrix as measures do not have relationships with categories on the left.
2. I would also like to do a waterfall or a bridge analysis but does not work for the same reason as 1.
If there is a way to create relationship between measures and a table or a better way of approaching this, that would be a tremendous help.
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
8 Replies
Hi hidenseek9 - Since measures don’t inherently belong to rows, the best approach is to unpivot your forecast data into a table format that matches the existing category structure.
FCF_Forecast_Table =
VAR FCF_2024 =
SELECTCOLUMNS(
{'FCF'}, "Macro Category", "FCF", "Year", "2024", "Value", [FCF_2024]
)
VAR FCF_Target =
SELECTCOLUMNS(
{'FCF'}, "Macro Category", "FCF", "Year", "2025 Target", "Value", [FCF_2025_Target]
)
VAR FCF_Forecast =
SELECTCOLUMNS(
{'FCF'}, "Macro Category", "FCF", "Year", "2025 Forecast", "Value", [FCF_2025_Forecast]
)RETURN UNION(FCF_2024, FCF_Target, FCF_Forecast)
Waterfall charts require a category-based column, not individual measures. Since your FCF forecast is stored in measures, you need to structure the data properly.
Ensure the FCF_Forecast_Table (from Step 1) is structured properly.
Create a waterfall chart in Power BI:
Category: "Macro Category"
Breakdown: "Year" (to show 2024 vs. 2025 vs. Forecast)
Values: "Value"
Adjust formatting so that the starting point is FCF 2024, and increments show changes leading to FCF 2025 Target/Forecast.
hope this helps.
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?
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
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
Thank you both for the suggestion.
pankajnamekar25 rajendraongole1
Are you not able to access my sample data?
As a test, I have created measures for forecast values for some category items.
Much appreciate your support!
Hi hidenseek9,
Thank you for reaching out to Microsoft Fabric Community.
Thank you pankajnamekar25 and rajendraongole1 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
