Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sowmiya
Helper III
Helper III

DAX

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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ok! Try this:

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

 

 

View solution in original post

7 REPLIES 7
themistoklis
Community Champion
Community Champion

@Sowmiya

 

Try this measure:

 

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

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

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

Anonymous
Not applicable

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

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
Not applicable

Ok! Try this:

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

 

 

Anonymous
Not applicable

It works for me!

 

13.PNG14.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.