Forum Discussion
DAX Calculating Average
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.
Please let me know if there are any questions. Thanks.
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
7 Replies
- Hi there, thanks for the details, would it be possible to post what you expect the output to look like?
Makes it easier to find a working solution.Thanks for your advice! Just updated question!
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?
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 GuThanks!!!!!!! That works!!!!!!! You just solved a huge problem of mine!! Thanks!!!!!
