cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sowmiya Member
Member

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

Accepted Solutions
Chiara Regular Visitor
Regular Visitor

Re: DAX

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
Chiara Regular Visitor
Regular Visitor

Re: DAX

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 Member
Member

Re: DAX

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

Chiara Regular Visitor
Regular Visitor

Re: DAX

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

Chiara Regular Visitor
Regular Visitor

Re: DAX

It works for me!

 

13.PNG14.PNG

Sowmiya Member
Member

Re: DAX

Hi @Chiara,
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?

themistoklis New Contributor
New Contributor

Re: DAX

@Sowmiya

 

Try this measure:

 

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

Re: DAX

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 480 members 4,892 guests
Please welcome our newest community members: