Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table called "Cost" that has Items and Monthly Costs:
Item | Cost Year | Jan_Cost | Feb_Cost | Mar_Cost | Apr_Cost | May_Cost |
A | 2022 | 10 | 10 | 11 | 12 | 14 |
B | 2022 | 14 | 15 | 15 | 16 | 17 |
C | 2022 | 17 | 19 | 21 | 20 | 19 |
Currently I have a measure (There are a lot of these measures and I need them for every Product):
I have to change the measure to dynamically pick up the column, so that I only change one value every month and all my measures start picking up the required column like:
Var ColName = "Jan_Cost"
SumCost = sumx(filter(cost, Cost[Cost YEAR]=2022), Cost[ColName])
Any help will be appreciated.
It's almost always a better option in these sorts of situations to unpivot your month columns so that your data looks like this instead:
Then SumCost is just SUM ( Cost[Cost] ).