topic DAX formula - How to turn count summarization to negative value in a stacked column visual in DAX Commands and Tips

DAX formula - How to turn count summarization to negative value in a stacked column visual

pradeepksahoo — Sun, 22 Aug 2021 10:40:27 GMT

I am trying to create a stacked column in BI. However, I would like one of the category values - in this case, the count of stage value of 0-Waiting to be shown in -ve. In other words, for Jan, Mar, Apr - the values of 2, 2, 1 should be -2, -2, -1.

Appreciate any help with DAX query or any other approach of how I can achieve in Power BI. Thanks in advance

An example in excel with data is shown below

Item	Month	State	Stage
1	Jan-21	In Process	0-Waiting
2	Jan-21	In Process	1-Started
3	Jan-21	Completed	2-Final
6	Jan-21	In Process	1-Started
7	Jan-21	In Process	0-Waiting
8	Jan-21	Completed	1-Started
1	Feb-21	In Process	1-Started
2	Feb-21	In Process	1-Started
1	Mar-21	In Process	1-Started
4	Mar-21	In Process	0-Waiting
5	Mar-21	In Process	0-Waiting
1	Apr-21	In Process	1-Started
2	Apr-21	Completed	2-Final
4	Apr-21	In Process	0-Waiting

Re: DAX formula - How to turn count summarization to negative value in a stacked column visual

mahoneypat — Sun, 22 Aug 2021 11:22:19 GMT

Here is a measure that shows one way to do it. Just change Items to your actual table name.

ItemCount =
VAR inprogress =
    CALCULATE (
        COUNT ( Items[Item] ),
        KEEPFILTERS ( Items[Stage] IN { "1-Started", "2-Final" } )
    )
VAR waiting =
    CALCULATE ( COUNT ( Items[Item] ), KEEPFILTERS ( Items[Stage] = "0-Waiting" ) )
RETURN
    0 + inprogress - waiting

Pat

Re: DAX formula - How to turn count summarization to negative value in a stacked column visual

pradeepksahoo — Sun, 22 Aug 2021 13:51:47 GMT

Hi mahoneypat ,

Many thanks for your prompt help. It seems to almost work. Except that I wanted another condition to be in the filter i.e. the state value of "In Process". The -ve count should not be applied for records which have state as "Completed"

Thanks again for your help

Pradeep

Re: DAX formula - How to turn count summarization to negative value in a stacked column visual

mahoneypat — Sun, 22 Aug 2021 15:34:31 GMT

Perhaps you already did this, but you can add this as another clause in the CALCULATE for that variable.

Items[State] <> "Completed"

Pat