Forum Discussion
Selecting Column Dynamically for a measure calculation with a Slicer
Hi,
Been banging my head against the wall for some time now but I seem to have run into a dead end for my issue.
I am rebuilding a manual Finance Forecast with the help of Power BI and am currently focusing on making it more dynamic.
Today they have two tables: One that contains traditional fact data such as revenue etc. and the other table contains different % values for every product, split in multiple different columns for different periods.
Tables Examples:
Revenue Table
| Product | Revenue |
A | 100 |
| B | 50 |
| C | 150 |
Rates Table
| Product | Jan-Apr | May-Aug | Sep - Dec |
A | 90% | 81% | 98% |
| B | 89% | 85% | 98% |
| C | 75% | 96% | 93% |
What I am trying to accomplish is to have a slicer in the report where the user can select the period they want to see calculations for and the Calculation updates accordingly.
If the user selects Jan-Apr it should return as:
| Product | Calculated Value |
A | 90% * 100 |
| B | 89% * 50 |
| C | 75% * 150 |
and the corresponding column values if they select May-Aug instead
Creating the measure for multiplying the data isn't a problem, the IF statement is there to handle some unexpected missing rates:
SUMX(
Sales,
Sales[Revenue] *
CALCULATE(
IF(
FIRSTNONBLANK('Annualization Factors'[Jan - Apr], 1)=0, 1,
FIRSTNONBLANK('Annualization Factors'[Jan - Apr], 1)
)
)
)
Conceptually it feels like it is something Field Parameters should be able to handle, simply replacing the "Annualization Factors[Jan-Apr]" part. However, I haven't been able to make this work.
Is there a way to utilize Field Parameters for this or any other way to solve my issue? Any help is appreciated
A bridge table is a viable option for handling many-to-many relationships. Here's a different approach that allows you to use the data model in my example, but without the relationship. I added rows to Sales such that each Product has multiple rows, and Product D doesn't have a row in Annualization Factors.
Calculated Value = VAR vDateRange = SELECTEDVALUE ( 'Annualization Factors'[Date Range] ) VAR vTable = ADDCOLUMNS ( VALUES ( Sales[Product] ), "@Revenue", CALCULATE ( SUM ( Sales[Revenue] ) ), "@Factor", VAR vProduct = Sales[Product] RETURN CALCULATE ( MAX ( 'Annualization Factors'[Value] ), 'Annualization Factors'[Product] = vProduct, 'Annualization Factors'[Date Range] = vDateRange ) ) VAR vResult = SUMX ( vTable, [@Revenue] * COALESCE ( [@Factor], 1 ) ) RETURN vResultIn case you're interested, a useful technique during testing is to replace the SUMX with the line below:
CONCATENATEX ( vTable, [@Revenue] & " | " & COALESCE ( [@Factor], 1 ), ", " )This allows you to see the underlying components of each data point in the matrix:
5 Replies
- DataInsights
Super User
A different approach is to unpivot the Annualization Factors table so it looks like this:
Data model:
Measure:
Calculated Value = SUMX ( Sales, Sales[Revenue] * MAXX ( RELATEDTABLE ( 'Annualization Factors' ), 'Annualization Factors'[Value] ) )Use Sales[Product] in visual:
DataInsights Thank you, pivoting works for my purpose but causes another issue due to changing the data structure.
The functionality is there and looks to be working as I wanted it to. However, the relationship between the revenue table and the annualization goes to a many-to-many instead of a one-to-many.
There are multiple lines for the same product on the revenue table, and unpivoting it creates multiple versions of a Product on both sides. I realize now that I should have included that in the sample table to avoid confusion.
While the slicer works perfectly in changing the rates, it creates an issue where there is Revenue for a Product, but it can't find a rate on the other side of the relationship. When selecting a period in the slicer, any Product without that relationship is filtered out, I would like it to be multiplied by 1 if the rate can't be found instead of being filtered out completelExample:
Product Revenue Rate Revenue Annualized Product A 50 0 50 Product B 60 0 60 Dexcom lacks the rate, but has claims in this case. If a slicer value is selected these 2 rows disappear completely instead of returning the Revenue value
Can the measure be modified to handle this or is there another way to tackle the data model?Thank you for your assistance!
Rasmus
I believe I figured out a workaround. I kept the original table unpivoted and had it act as a bridge table to the unpivoted one. That way I didn't break the one-to-many relationship and the measure now acts as intended.
Unsure if this is best practice however
- DataInsights
A bridge table is a viable option for handling many-to-many relationships. Here's a different approach that allows you to use the data model in my example, but without the relationship. I added rows to Sales such that each Product has multiple rows, and Product D doesn't have a row in Annualization Factors.
Calculated Value = VAR vDateRange = SELECTEDVALUE ( 'Annualization Factors'[Date Range] ) VAR vTable = ADDCOLUMNS ( VALUES ( Sales[Product] ), "@Revenue", CALCULATE ( SUM ( Sales[Revenue] ) ), "@Factor", VAR vProduct = Sales[Product] RETURN CALCULATE ( MAX ( 'Annualization Factors'[Value] ), 'Annualization Factors'[Product] = vProduct, 'Annualization Factors'[Date Range] = vDateRange ) ) VAR vResult = SUMX ( vTable, [@Revenue] * COALESCE ( [@Factor], 1 ) ) RETURN vResultIn case you're interested, a useful technique during testing is to replace the SUMX with the line below:
CONCATENATEX ( vTable, [@Revenue] & " | " & COALESCE ( [@Factor], 1 ), ", " )This allows you to see the underlying components of each data point in the matrix:
Thank you for your assistance, really appreciated.
Will use your code to develop my understanding for the future
Best,
Rasmus
