Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |