Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarize

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.