Hi There,
I have 10 tables from 3 different sources, as per the business reqirement we have merged the tables with left outer join and made one single table with required columns. On top of that we created some measures and columns to get the total business spend.
Till now all the measures are working fine and matching with the data. When ever i split the measure to see the yearwise spend i am getting huge variation in Spend when sum them all together.
Can any one let me now where i am doing wrong to calculate the yearwise spend.
Below you can find the DAX for the Measure calculation and some data samples.
@amitchandak @tamerj1
Total Business Spend = CALCULATE(SUMX(SUMMARIZE(Merge_table,Merge_table[Project],Merge_table[unit],Merge_table[code], "MAX",MAX(Merge_table[Spend])),[MAX])).
Note: Merge_table[Spend] is custom column derived with business formulae.
The above dax have derived to allimenate the duplicate values and get the exact value for Spend.
now when i try to find the Yearwise spend by the below dax.
Spend YTD = CALCULATE(SUMX(SUMMARIZE(Merge_table,Merge_table[Project],Merge_table[unit],Merge_table[code], "MAX",MAX(Merge_table[Spend])),[MAX]),Merge_table[date])
Total business spend = 2670M
spend Ytd =
| 2020 | 1100 |
| 2021 | 900 |
| 2022 | 1400 |
| 2023 | 200 |
| 2024 | 1800 |
| Total | 5400 |
Now i want to split Total business spend = 2670 in Yearwise, Where sum of all year should be 2670.
Thanks for the help in advance.
Regards
Power Mate
