Summarize

[Anonymous]

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

[Anonymous · Accepted 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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin]

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