Help in dax grouping formulas

[Anonymous]

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

[v-lili6-msft]

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

[Anonymous · Accepted solution]

Solve it using :

% of Total transaction No group by = DIVIDE((events_tpr_card[Number of transaction]);CALCULATE(events_tpr_card[Number of transaction];All(events_tpr_card[eventType];events_tpr_card[sequenceNumber])))

[MFelix]

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