Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative value of a constant value

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

  • Value for week 1 = 0,50
  • Value for week 2 = 1,00
  • Value for week 3 = 1,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:

 

Cumulative Totals.png

Any ideas how to figure out the metric to calculate the cumulative value for my constant?

 

Thanks a lot in advanced!

 

 

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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().)

1.png

Then add both 2 cumulative measures to the values pane, and format the second one.

2.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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().)

1.png

Then add both 2 cumulative measures to the values pane, and format the second one.

2.png

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.