Total Revenue (in Groups)

DAX DAX Calculations
12

@Melissa Here is a DAX Solution, just don’t sue me for plagiarism :rofl: :rofl:

image
image1105×556 25.8 KB

image

Antriksh = 
VAR OriginalData =
    ALL ( 'Sample Table' )
VAR ItemList =
    FILTER (
        ALL ( 'Sample Table'[ITMCODE] ),
        CONTAINSSTRING ( 'Sample Table'[ITMCODE], "A" )
    )
VAR LimitColumns =
    ALLEXCEPT ( 'Sample Table', 'Sample Table'[AMOUNT] )
VAR FilterItems =
    FILTER ( LimitColumns, 'Sample Table'[ITMCODE] IN ItemList )
VAR Count_ITMCODE =
    ADDCOLUMNS (
        FilterItems,
        "Count",
            VAR CurrentCode = 'Sample Table'[SLIP]
            VAR SameRows =
                FILTER ( FilterItems, 'Sample Table'[SLIP] = CurrentCode )
            VAR Result =
                COUNTROWS ( SameRows )
            RETURN
                Result
    )
VAR FinalTable =
    ADDCOLUMNS (
        Count_ITMCODE,
        "Amount w/o ITMCODE",
            VAR CurrentSlip = 'Sample Table'[SLIP]
            VAR FilterRows =
                FILTER (
                    OriginalData,
                    'Sample Table'[SLIP] = CurrentSlip
                        && NOT 'Sample Table'[ITMCODE] IN ItemList
                )
            VAR Result =
                SUMX ( FilterRows, 'Sample Table'[AMOUNT] )
            RETURN
                Result,
        "Amount with ITMCODE",
            VAR CurrentSlip = 'Sample Table'[SLIP]
            VAR CurrentCode = 'Sample Table'[ITMCODE]
            VAR FilterRows =
                FILTER (
                    OriginalData,
                    'Sample Table'[SLIP] = CurrentSlip
                        && 'Sample Table'[ITMCODE] = CurrentCode
                )
            VAR Result =
                SUMX ( FilterRows, 'Sample Table'[AMOUNT] )
            RETURN
                Result
    )
VAR PrelimResult =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FinalTable,
            "Final",
                [Amount w/o ITMCODE] / [Count] + [Amount with ITMCODE]
        ),
        "Slip", 'Sample Table'[SLIP],
        "ITMCODE", 'Sample Table'[ITMCODE],
        "Slip Revenue", [Final]
    )
VAR Result =
    SUMX (
        FILTER ( PrelimResult, [ITMCODE] IN VALUES ( 'Sample Table'[ITMCODE] ) ),
        [Slip Revenue]
    )
RETURN
    Result

sample table.pbix (50.3 KB)

3 Likes