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,
Im new to DAX and currently stucked trying to get following result, i have a Rawdata table and below i show a ResultTable that i would like to have as a result, it should aggregates the data as it is described in the column Total in the ResultTable:
RawdataTable
Date | Factory | Shift | Qty |
01/08/2021 | A | Morning | 5 |
01/08/2021 | A | Afternoon | 11 |
01/08/2021 | A | Night | 4 |
02/08/2021 | A | Morning | 7 |
02/08/2021 | A | Afternoon | 8 |
02/08/2021 | A | Night | 6 |
ResultTable:
Date | Factory | Total |
02/08/2021 | A | = (01/08/2021|Night|Qty*0.75) + (02/08/2021|Morning|Qty*1.0) + (02/08/2021|Afternoon|Qty*1.0) + (02/08/2021|Night|Qty*0.25) = 19.5 |
If i simply use summarize group by date and factory i would get a wrong 21 in that example. Any idea or hint on how i can achieve this is very appreciated?
Thanks a lot
Edit: changed picture for actual tables in order to make it easier to copy paste
Solved! Go to Solution.
Hi @Anonymous ,
Please use the following formula to create a measure:
Measure =
VAR _pre =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date]
= MAX ( 'Table'[Date] ) - 1
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] = "Night"
)
) * 0.75
VAR _mornAndAfter =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date] = MAX ( 'Table'[Date] )
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] IN { "Morning", "Afternoon" }
)
)
VAR _night =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date] = MAX ( 'Table'[Date] )
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] = "Night"
)
) * 0.25
RETURN
_pre + _mornAndAfter + _night
Here is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please use the following formula to create a measure:
Measure =
VAR _pre =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date]
= MAX ( 'Table'[Date] ) - 1
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] = "Night"
)
) * 0.75
VAR _mornAndAfter =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date] = MAX ( 'Table'[Date] )
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] IN { "Morning", "Afternoon" }
)
)
VAR _night =
CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLSELECTED ( 'Table' ),
[Date] = MAX ( 'Table'[Date] )
&& [Factory] = MAX ( 'Table'[Factory] )
&& [Shift] = "Night"
)
) * 0.25
RETURN
_pre + _mornAndAfter + _night
Here is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
if you are new to DAX then this is a pretty tall ask.
You didn't say if you wanted this as a measure or calculated column. Here's a calculated column version
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |