Need help with DAX for percentage of filtered total with a twist

[RainyClouds]

Hello all,

I'm trying to calculate a couple of measures in DAX using this dummy set of data from the table below (I cannot provide real data due to the nature of the project):

SalesPerson	Date	PaymentType	Location	Category	TransactionFlag	Amount
Daisy	11-Jan	Cash	Metro	Dongle	0	10
Daisy	11-Jan	Cash	Regional	Widget		15
Daisy	11-Jan	Card	Metro	Widget	1	100
Daisy	10-Jan	Cash	Metro	Widget		12
Daisy	10-Jan	Cash	Regional	Widget	1	17
Daisy	10-Jan	Card	Metro	Dongle	0	120
Dean	11-Jan	Cash	Metro	Dongle	0	11
Dean	11-Jan	Cash	Regional	Widget	0	16
Dean	11-Jan	Card	Metro	Widget	0	110
Dean	10-Jan	Cash	Metro	Widget	0	13
Dean	10-Jan	Cash	Regional	Widget	0	18
Dean	10-Jan	Card	Metro	Dongle	0	130
Gillian	11-Jan	Cash	Metro	Dongle	0	9
Gillian	11-Jan	Cash	Regional	Widget	0	14
Gillian	11-Jan	Card	Metro	Widget	1	90
Gillian	10-Jan	Cash	Metro	Widget	0	18
Gillian	10-Jan	Cash	Regional	Widget	0	19
Gillian	10-Jan	Card	Metro	Dongle	0	80
Gibbo	11-Jan	Cash	Metro	Dongle	0	20
Gibbo	11-Jan	Cash	Regional	Widget	0	21
Gibbo	11-Jan	Card	Metro	Widget		140
Gibbo	10-Jan	Cash	Metro	Widget	0	30
Gibbo	10-Jan	Cash	Regional	Widget	0	8
Gibbo	10-Jan	Card	Metro	Dongle		105

*Note that in the table above, the TransactionFlag can have values of 1 (for true), 0 (for false), or blank (for not applicable).

The two measures are for Flagged Transaction Amount (those where TransactionFlag = 1) as a percentage of a SalesPerson's total Amounts; and then Flagged Transaction Amount as a percentage of all SalesPerson's total amounts who have a flagged transaction. I will also want to be able to slice the report by all dimensions. It is important that all of a SalesPerson's amounts are included in the total if and only if they have a flagged transaction in whatever slice has been selected.

So effectively I want to be able to get to a table like this:

SalesPerson	Date	PaymentType	Location	Category	Amount	Flagged Amount as a % of SalesPerson Total Amount	Flagged Amount as a % of All SalesPerson with FlaggedTransaction Total Amount
Daisy	11-Jan	Card	Metro	Widget	100	80.00	42.02
Daisy	10-Jan	Cash	Regional	Widget	17	11.41	11.41
Gillian	11-Jan	Card	Metro	Widget	90	79.65	37.82

And like this:

SalesPerson	PaymentType	Category	Amount	Flagged Amount as a % of SalesPerson Total Amount	Flagged Amount as a % of All SalesPerson with FlaggedTransaction Total Amount
Daisy	Card	Widget	117	79.59	43.66
Gillian	Card	Widget	90	74.38	33.58

Can anyone help me with the DAX for these measures? I'm not really even sure where to start. Thanks!

[Anonymous · Accepted solution]

Hi @RainyClouds,

You can use following measures to achieve your requirement, it will dynamic changes based on category fields:

% of SalePerson =
DIVIDE (
    SUM ( Sales[Amount] ),
    CALCULATE (
        SUM ( Sales[Amount] ),
        ALLSELECTED ( Sales ),
        VALUES ( Sales[SalesPerson] )
    ),
    -1
)

% of ALL SalePerson =
DIVIDE (
    SUM ( Sales[Amount] ),
    CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED ( Sales ) ),
    -1
)

Regards,

Xiaoxin Sheng