Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Cumulative Total using measures

I have a dataset that has multiple timestamps for assets. I'm needing to retain the timestamps because I'll have a slider that allows you to adjust all of the visuals based on the timestamp. However, many of the measures I'm needing to calculate need to be done at the asset level. Here's an example of what my data looks like:

 

 

The yellow fields are present in the data and I have meaures to calculate the 3 orange columns. Here is how I'm calculating those. 

 

total_benefit = SUM(table[benefit])

benefit_by_asset = CALCULATE([total_benefit], ALLEXCEPT(table, table[Asset]))

total_cost= SUM(table[cost])

cost_by_asset = CALCULATE([total_cost], ALLEXCEPT(table, table[Asset]))

 

benefit_rank =
RANKX(
    ALLSELECTED(table[Asset ID]),
    [benefit_by_asset],
    ,
    DESC
)
 
Up to this point things are straightforward. However, where I'm struggling is I now need to calculate the cumulative cost based on that rank. And then I need to have this respond to a parameter slider based on the budget. I've spent several hours and cannot get this to work. Any help would be greatly appreciated!
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I'm able to get a working cumulative cost with this measure:

    cum_cost = SUMX(TOPN([benefit_rank], CALCULATETABLE(VALUES(table[asset]), ALLSELECTED(table[asset])),[benefit_rank], ASC), [cost_by_asset]).
     
    Then I've added another measure that calculates whether the cumulative cost is greater or equal to the budget parameter (slider). 
     
    ind_cost_within_budget = IF([cum_cost] <= [Budget Value], 1, 0)
     
    Now I need to calculate the total benefit for those assets that are within the budget. 
     
    This is what I have so far, but it just returns the total benefit, I need it to filter out the assets that aren't within the specified budget. 
    benefit_within_budget = CALCULATE(SUM(table[benefit]), FILTER(table, [ind_cost_within_budget]=1)).
     
    Maybe I need to take a different approach here? Any thoughts?
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

      You want to display the cumulative cost is greater or equal to the budget, you sholuld change the measure:

       

      ind_cost_within_budget = IF([cum_cost] >= [Budget Value], 1, 0)

       

      and can you provide some sample data and the expected output you want?

       

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.