Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create a Cumulative Sum of a constant value of a parameter table with no relation with my Fact Table, neither my Date Table, in order to calculate a cumulative value of that value for each week. It would be something like this:
Constant Value = 0,50
The object I am aiming is to represent this Objetive Cumulative Value (Green dot line) vs my Current Cumulative Value (Solid orange line) of my Fact Table, looking something like this:
Any ideas how to figure out the metric to calculate the cumulative value for my constant?
Thanks a lot in advanced!
Solved! Go to Solution.
Hi @Anonymous
I created a sample as your requested, there’s date table relates to fact table1 ,and I add another new table2 managing the relationship 1:1 with fact table1.
Cumulative value1 = CALCULATE(SUMX(Table1,Table1[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[week])) Cumulative value2 = CALCULATE(SUMX(Table2,Table2[value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table2[weeknum]))
(week and weeknum is calculated columns using Weeknum().)
Then add both 2 cumulative measures to the values pane, and format the second one.
Best regards,
Dina Ye
Hi @Anonymous
I created a sample as your requested, there’s date table relates to fact table1 ,and I add another new table2 managing the relationship 1:1 with fact table1.
Cumulative value1 = CALCULATE(SUMX(Table1,Table1[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table1[week])) Cumulative value2 = CALCULATE(SUMX(Table2,Table2[value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),VALUES(Table2[weeknum]))
(week and weeknum is calculated columns using Weeknum().)
Then add both 2 cumulative measures to the values pane, and format the second one.
Best regards,
Dina Ye
HI @Anonymous ,
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
Hi @v-diye-msft
Sorry not to answer before, I've been out for holidays 🙂
Finally, I approached this issue in a different way. It was an idea i get from @marcorusso in www.daxpattern.com :
You can take a look here
I will also try your solution and let you know if it works.
Anyway, thanks a lot for your help.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |