DAX

[Sowmiya]

Hi Community,
Help me with the DAX to Display Total in Card.

Id and Count, Eliminate are Raw Data.
Target is dynamic user input. 
Difference is Count-Target (Obtained by DAX)
Negative Difference is obtained by DAX - Abs of Negative Value in Difference

Help me with DAX to display the total of Negative Value in Card Visual (i.e 56).

  
Thanks in advance,
Sowmiya

[themistoklis]

@Sowmiya

Try this measure:

Measure = CALCULATE((SUM('Table'[Count]) - SUM('Table'[Target])), FILTER(ALLEXCEPT('Table', 'Table'[ID]), 'Table'[Count] < 'Table'[Target]))

[Anonymous]

Hi @Sowmiya,

You can create the calculate column [Negative Difference] with this formula:

= IF(Table[Difference]< 0,ABS(Table[Difference]),0)

Then you can create a measure:

Negative Value = SUM (Table[Negative Difference])

After that put this measure in Card Visual.

Best Regards 

Chiara

[Sowmiya]

HI,
No it does not work, Argument of SUM accepts only Column not Measure (Table[Negative Difference])

[Anonymous]

As I have written, You must create a calculate column [Negative Difference], and pass it as the argument of SUM.

[Sowmiya]

Hi @Anonymous,
In Screenshot, It is a table Visual.

Count  is obtained by count of each ID. ID 35 has occured 254 times , ID 36 - 290 times. In my case, Negative Difference DAX cannot be added as Column. It must be added as measure. How can I visualize the Total of Negative Difference in Card visual using DAX in measure?

[Anonymous · Accepted solution]

Ok! Try this:

Negative Value =
SUMX (
    SUMMARIZECOLUMNS (
        'Table'[ID],
        "Diff", [Difference],
        "Negative Difference", IF ( [Difference] < 0, ABS ( [Difference] ), 0 )
    ),
    [Negative Difference]
)

[Anonymous]

It works for me!