Forum Discussion

GBKYE2's avatar
GBKYE2
Frequent Visitor
4 years ago
Solved

Summing and Slicing multiple columns

Hello,   I've been given some data to build a report and part of it involves summarizing some data across multiple columns. Example:   Incident Root Cause 1 Root Cause 2 Root Cause 3 Root ...
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    GBKYE2 don't change to 0 🙂
    Try this:

    RootSlicer = 
    VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer])
    VAR _result = 
    SUMX(
    	'Table',
    	If(
    	    'Table'[Root Cause 1] in _slicer_values
    	    || 'Table'[Root Cause 2] in _slicer_values
    	    || 'Table'[Root Cause 3] in _slicer_values
    	    || 'Table'[Root Cause 4] in _slicer_values,
    	    1,
    	    blank()
        )
    )
    RETURN
    	_result


    or this:

    RootSlicer = 
    VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer])
    VAR _result = 
    SUMX(
    	SUMMARIZE(
    		'Table'
    		'Table'[Incident],
    		'Table'[Root Cause 1],
    		'Table'[Root Cause 2],
    		'Table'[Root Cause 3],
    		'Table'[Root Cause 4]
    	),
    	If(
    	    MIN('Table'[Root Cause 1]) in _slicer_values
    	    || MIN('Table'[Root Cause 2]) in _slicer_values
    	    || MIN('Table'[Root Cause 3]) in _slicer_values
    	    || MIN('Table'[Root Cause 4]) in _slicer_values,
    	    1,
    	    blank()
        )
    )