DAX measure to return alternative result for total

Question

How to create DAX measure returning different value for total in table visual? I would like it for conditional formatting for whatever dimension split in table visuals. But since conditional formatting does not work for totals I do not want to display it for that line.

I need something like:

IF(condition_identifying_total_line, "Alternative result", [TrafficLightIcon])

Edit. This does exactly what I want but I hope for more elegant approach or any other suggestions:

IsTotal =
COUNTROWS(FactTable) =
CALCULATE (
    COUNTROWS ( FactTable ),
    ALLSELECTED ( FactTable)
)

This measure works for whatever dimension split of Sales figures in table visual.

score 1 · Answer 1 · answered Jul 26 '19 at 14:15

1

There are a variety of options depending on exactly what you want to do. I suggest taking a look a the following functions for ideas:

For example, if Sales broken out by a column A, here are a couple possible approaches:

Sales = IF( HASONEVALUE( T[A] ), SUM ( T[Sales] ), <Alternative Result> )

Sales = IF( ISFILTERED ( T[A] ), <Alternative Result>, SUM ( T[Sales] ) )

answered Jul 26 '19 at 14:15

Alexis Olson

38,724
7
42
64

My question is related to https://stackoverflow.com/questions/57202628/traffic-lights-in-power-bi-which-work-also-for-totals The measure `TrafficLight = UNICHAR(11044)` after conditional formatting show nasty black spot in total line, which is not affected by conditional formatting. I do not want it to show up. I want to detect totals, regardless if it is filtered or not. – Przemyslaw Remin Jul 30 '19 at 20:45

score 1 · Answer 2 · answered Jul 26 '19 at 19:19

1

You can find full documentation for how to handle granularities from the SQLBI website here: https://www.daxpatterns.com/handling-different-granularities/

Hope this helps! William

answered Jul 26 '19 at 19:19

William Rodriguez

41
3

Przemyslaw Remin · Accepted Answer · 2019-08-22T11:49:30.900

0

I have ended up using INSCOPE function:

IsTotal = NOT(
   ISINSCOPE(products[dimension1])
|| ISINSCOPE(products[dimension2])
|| ISINSCOPE(stores[dimension1])
|| ISINSCOPE(stores[dimension2])
)

Unfortunately it requires hard coding all dimensions by which we want to slice or group visuals.

edited Aug 22 '19 at 11:49

answered Jul 26 '19 at 12:57

Przemyslaw Remin

6,276
25
113
191

DAX measure to return alternative result for total

3 Answers3