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.
Hi,
Can you please help in creating a measure using DAX to fulfil the 4 conditions below? Also the total amount should be summing up the rows after the conditions are applied.
1. If Company, Cost Center & Posting Period for Doc Type "PY" is the same as "PD", take "PD" amount
2. In the absence of "PD" for the same Company, Cost Center & Posting Period, take "PY" amount
3. In the absence of "PY" for the same Company, Cost Center & Posting Period, do not take "PD" amount
4. The rest of the Doc Type i.e. AP or JE should remained unchanged
Sample data:
Fact Table
Company | Cost Center | Posting Period | Doc Type | Amount |
50 | 2222222 | Apr-21 | PY | (30,000.00) |
50 | 2222222 | Apr-21 | PD | (30,000.00) |
60 | 1111111 | Apr-21 | PY | (16,000.00) |
60 | 1111111 | Apr-21 | PD | (16,000.00) |
50 | 2222222 | Apr-21 | JE | (12,000.00) |
50 | 2222222 | May-21 | PD | (15,000.00) |
60 | 1111111 | May-21 | PD | (8,000.00) |
60 | 1111111 | May-21 | JE | (14,000.00) |
50 | 2222222 | May-21 | AP | (10,000.00) |
50 | 2222222 | Jun-21 | PY | (24,000.00) |
(175,000.00) |
Result:
Company | Cost Center | Posting Period | Doc Type | Amount |
50 | 2222222 | Apr-21 | PD | (30,000.00) |
60 | 1111111 | Apr-21 | PD | (16,000.00) |
50 | 2222222 | Apr-21 | JE | (12,000.00) |
60 | 1111111 | May-21 | JE | (14,000.00) |
50 | 2222222 | May-21 | AP | (10,000.00) |
50 | 2222222 | Jun-21 | PY | (24,000.00) |
(106,000.00) |
I appreciate any help. Thanks!
@Joanne Try this, PBIX is attached below signature. You want Table28 and Page 6.
Measure 3 =
VAR __PY = MAXX(FILTER('Table28',[Doc Type]="PY"),[ Amount ])
VAR __PD = MAXX(FILTER('table28',[Doc Type]="PD"),[ Amount ])
RETURN
SWITCH(TRUE(),
NOT(ISBLANK(__PD)) && NOT(ISBLANK(__PY)),__PD,
NOT(ISBLANK(__PY)) && ISBLANK(__PD),__PY,
NOT(ISBLANK(__PD)) && ISBLANK(__PY),BLANK(),
MAX('Table28'[ Amount ])
)
Measure 3 Total =
VAR __Table = SUMMARIZE('Table28',[Company],[Cost Center],[Posting Period],[Doc Type],"__Measure",[Measure 3])
RETURN
IF(HASONEVALUE('Table28'[Company]),[Measure 3],SUMX(__Table,[__Measure]))
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |