Forum Discussion
Help in dax grouping formulas
Hi guys,
Can anybody help me to create formulas from red columns ??
% of total transactions event_type = D2/SUMA($D$2:$D$14)
AVG from last 30 days by event type = the same rule so avarge number of transaction and grouping is by all columns with values for example 1 row: AE,0,TEV_CREATED
| trx_snapshot_denormalized.cardInfo_country | sequenceNumber | eventType | Number of transaction | % of total transactions event_type | AVG from last 30 days by event type |
| AE | 0 | TEV_CREATED | 19 | 14% | 16,5 |
| AE | 1 | TEV_PAYMENT_INFO_UPDATED | 19 | 14% | 12,3 |
| AE | 2 | TEV_SECURITY_CHECK_REQUESTED | 19 | 14% | 12,3 |
| AE | 3 | TEV_SECURITY_CHECK_APPROVED | 18 | 14% | 0,002 |
| AE | 3 | TEV_SECURITY_CHECK_REJECTED | 1 | 1% | |
| AE | 4 | TEV_THREE_DS_VERIFICATION_STARTED | 18 | 14% | |
| AE | 5 | TEV_AUTHORISATION_ATTEMPT | 12 | 9% | |
| AE | 5 | TEV_REJECTED | 1 | 1% | |
| AE | 6 | TEV_AUTHORISATION_REJECT | 8 | 6% | |
| AE | 6 | TEV_AUTHORISED | 4 | 3% | |
| AE | 7 | TEV_ACCEPTED | 4 | 3% | |
| AE | 7 | TEV_REJECTED | 8 | 6% | |
| AE | 8 | TEV_CANCELED | 1 | 1% | |
| AG | 0 | TEV_CREATED | 1 | 13% | |
| AG | 1 | TEV_PAYMENT_INFO_UPDATED | 1 | 13% | |
| AG | 2 | TEV_SECURITY_CHECK_REQUESTED | 1 | 13% | |
| AG | 3 | TEV_SECURITY_CHECK_APPROVED | 1 | 13% | |
| AG | 4 | TEV_THREE_DS_VERIFICATION_STARTED | 1 | 13% | |
| AG | 5 | TEV_AUTHORISATION_ATTEMPT | 1 | 13% | |
| AG | 6 | TEV_AUTHORISATION_REJECT | 1 | 13% | |
| AG | 7 | TEV_REJECTED | 1 | 13% |
Many thx for he
3 Replies
- MFelix
Super User
Hi Anonymous ,
First of all don't know if the naming of the columns is correct but I did the following measure:
% over transaction type = SUM ( 'Table'[CountrysequenceNumbereventType] ) / CALCULATE ( SUM ( 'Table'[CountrysequenceNumbereventType] ); ALL ( 'Table'[CountrysequenceNumbereventType] ); ALLEXCEPT ( 'Table'; 'Table'[trx] ) )Regading the last column how do you calculate the number? I can understand the formula for the first row (132 / 😎 but not for the next ones.
Regards,
MFelix
v-lili6-msftCommunity Support
hi Anonymous
You could use EARLIER Function to create a calculate column as below:
https://docs.microsoft.com/en-us/dax/earlier-function-dax
https://powerpivotpro.com/2012/03/the-correct-usage-of-earlier/
% over transaction type column = 'Table'[Number of transaction] / CALCULATE ( SUM ( 'Table'[Number of transaction] ), FILTER( 'Table', 'Table'[trx_snapshot_denormalized.cardInfo_country] =EARLIER('Table'[trx_snapshot_denormalized.cardInfo_country])))For the last column. it has the same logic.
Regards,
Lin
