Forum Discussion
DAX Calculating Average
Hi Bonjuga,
Please try below measures:
days for received IR = CALCULATE ( SUM ( Sheet2[Days] ), FILTER ( Sheet2, Sheet2[Status] = "received from IR" ) ) days for sent = CALCULATE ( SUM ( Sheet2[Days] ), FILTER ( FILTER ( Sheet2, CALCULATE ( COUNT ( Sheet2[Transaction ID] ), ALLEXCEPT ( Sheet2, Sheet2[Transaction ID] ) ) = 3 ), Sheet2[Status] = "sent" ) ) Count transaction = CALCULATE ( DISTINCTCOUNT ( Sheet2[Transaction ID] ), FILTER ( Sheet2, Sheet2[Status] = "received from IR" ) ) Average = ([days for received IR]-[days for sent])/[Count transaction]
Best regards,
Yuliana Gu
Hi Guys,
I have a database that has data looks like following:
| Transaction ID | Status | Days |
| 1 | received | 1 |
| 1 | sent | 2 |
| 2 | received | 3 |
| 2 | sent | 4 |
| 2 | received from IR | 5 |
| 3 | received | 6 |
| 3 | sent | 7 |
| 3 | received from IR | 8 |
| 4 | received | 9 |
| 4 | sent | 10 |
so, for each transaction, there are either two or three status: :"received", "sent", or "received from IR". Now I am very interested in average time between "received from IR" and "sent". That means, I need to extract those Transactions that have " received from IR"(for example, transaction 2 and 3), then use "received from IR" days minus "sent" days.
The expected output would be ((5-4)+(8-7))/2, so it is (("received from IR" from transaction 2- "sent" from transaction2)+("received from IR" from transaction 3 - "sent" from transaction 3))/2
Is there anyway I could do that with DAX? Also, I am currently using Azure database, which means I cannot create new column, I can only create new meausres.
You should be able to use CALCULATE with a measure to do what you want. So, for example:
Measure = CALCULATE(SUM([Days]),FILTER(Table,[Status]="received"))
You could create a similar one for "sent" and then perhaps a third measure to do your calculation?
Thanks for your reply!! However, I don't want transaction 1 and 4, how can I filter them out so they won't affect my calculation?