Forum Discussion
Running Total for a calculated measure
Hello,
I created a measure to find the time difference between two timestamps. The timestamps table looks like this:
I used this formula to create a measure that calculates the time difference between these two columns:
It does not even let me make changes to the data to make it a total. Even when I try doing the Running total quick measure, it does not work. Do you know what I can do to get a total for this?
You will need to use SUMX, Like the example below. Note that I use the entire "Source" table as table expression. That will not give you the best performance, you might want to replace that with a column expression that matches the cardinality of the rows, maybe something like VALUES('Source'[Source ID]) .
SUMX('Source', CALCULATE( DATEDIFF(MIN(Source[Appeared]), MIN(Source[Disappeared]), MINUTE) + (SECOND(MIN(Source[Disappeared])) - SECOND(MIN(Source[Appeared]))) / 60 ))
3 Replies
- lbendlin
Super User
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
sjoerdvnSolution Sage
You will need to use SUMX, Like the example below. Note that I use the entire "Source" table as table expression. That will not give you the best performance, you might want to replace that with a column expression that matches the cardinality of the rows, maybe something like VALUES('Source'[Source ID]) .
SUMX('Source', CALCULATE( DATEDIFF(MIN(Source[Appeared]), MIN(Source[Disappeared]), MINUTE) + (SECOND(MIN(Source[Disappeared])) - SECOND(MIN(Source[Appeared]))) / 60 ))Hello,
Thank you for your reply. The code works. I had a similar doubt of making the same as an average. I used this DAX code:
Measure 2 = AVERAGEX(KEEPFILTERS(VALUES(Source[Component])), CALCULATE((DATEDIFF(MIN(Source[Appeared]), MIN(Source[Disappeared]), MINUTE) +(SECOND(MIN(Source[Disappeared])) - SECOND(MIN(Source[Appeared]))) / 60)))But it is not giving me the right answer.
