Seeking DAX solution

[bob57]

Hello,

My dataset includes this table callled Interests:

An interest that appears in column [Interest 1] is scored a 3, [Interest 2] a 2, and [Interest 3] a 1. Can you help by providing a DAX expression that will produce the following table visualization? The table displays the cumulative score fore each interest.

Thank you,

Bob

[smpa01 · Accepted solution]

@bob57  firstly you need a slicer table

slicer =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( 'Table'[Interest1] ),
            VALUES ( 'Table'[Interest2] ),
            VALUES ( 'Table'[Interest3] )
        )
    ),
    [Interest1] <> BLANK ()
)

then a measure

Measure = 
VAR _cal1 =
    CALCULATE (
        COUNT ( 'Table'[Interest1] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest1] )
    ) * 3
VAR _cal2 =
    CALCULATE (
        COUNT ( 'Table'[Interest2] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest2] )
    ) * 2
VAR _cal3 =
    CALCULATE (
        COUNT ( 'Table'[Interest3] ),
        TREATAS ( { MAX ( slicer[Interest1] ) }, 'Table'[Interest3] )
    ) * 1
RETURN
    _cal1 + _cal2 + _cal3

if you need subtotal

subtotal =
SUMX (
    ADDCOLUMNS (
        slicer,
        "x",
            VAR _cal1 =
                CALCULATE (
                    COUNT ( 'Table'[Interest1] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest1] )
                ) * 3
            VAR _cal2 =
                CALCULATE (
                    COUNT ( 'Table'[Interest2] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest2] )
                ) * 2
            VAR _cal3 =
                CALCULATE (
                    COUNT ( 'Table'[Interest3] ),
                    TREATAS ( { CALCULATE ( MAX ( slicer[Interest1] ) ) }, 'Table'[Interest3] )
                ) * 1
            RETURN
                _cal1 + _cal2 + _cal3
    ),
    [x]
)

[bob57]

This solves my problem. Thank you for you time and effort. Much appreciated. And a sound learning experience for me.