Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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 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]
)
This solves my problem. Thank you for you time and effort. Much appreciated. And a sound learning experience for me.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |