cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillianVictor
Frequent Visitor

DISTINCTCOUNT values difference between AXIS visual and card visual

Hello, guys!

I need some help over here, if you could so, I appreciate.

 

I'm using a DISTINCTCOUNT to get the distinct values from column A applying this measure to a Card, but when I apply to a Clustered Column Chart, this doesn't working fully.

 

If you sum the values from the Column Chart the total will be different.

Like this (the number format from the visuals is from Brazil, so dot is comma and comma is dot):

MyMeasure = DISTINCTCOUNT(Table1[ColumnA])

x.PNG

Let's add up the values from column chart:

4520+488+108+99+23+1 =5239

 

How can you see, the total values is different between Card and Column Chart distributed by AXIS labels.

 

The question is: how could I use the same total from Card applied to the Column Chart?

 

Bellow samples of my dataset and PBIX to make it more easy to understand.

Dataset

https://drive.google.com/open?id=1Mhg0QPpPxTPxmo_q2OYYwLn4xbiV47J1

PBIX

https://drive.google.com/open?id=1IPeK7j5uDWIU2517T8D_7l6Lh5nmCtmh

 

 

Best

Willian Victor

1 ACCEPTED SOLUTION

Hi @WillianVictor ,

 

Here we go.

Measure 2 = 
VAR s =
    MAX ( Planilha1[Situação] )
VAR no =
    CALCULATETABLE (
        VALUES ( Planilha1[Ordens] ),
        FILTER ( ALL ( Planilha1 ), Planilha1[Situação] <> s )
    )
RETURN
    IF (
        ISFILTERED ( Planilha1[Situação] ),
        CALCULATE (
            DISTINCTCOUNT ( Planilha1[Ordens] ),
            FILTER ( Planilha1, NOT ( Planilha1[Ordens] IN no ) )
        ),
        DISTINCTCOUNT ( Planilha1[Ordens] )
    )

22222.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

Hi @WillianVictor ,

 

Here we go.

Measure = SUMX(VALUES(Planilha1[Situação]),CALCULATE(DISTINCTCOUNT(Planilha1[Ordens])))

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi, @v-frfei-msft 

 

Thanks for your help! It's a good way too.

But I was trying to do the contrary.. apply the total of the first card (5,227) to the situation chart (when adding up all the situations, have  5,227 too).

 

How I said to @az38 

I don't even know if this will work fully.. but I wanna give a try before use a new dataset without these double orders.

(The original dataset came from my company and run by invoice and not by orders, so if I've to change it.. it'll take a while. And I don't wanna it).

Greg_Deckler
Super User IV
Super User IV

Number of ways. Most obvious is that you have a visual level filter on your column chart visual. Another way might be that the x-axis is coming from some other table and there are non-matching rows.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, Greg!

 

Have no filter on my visual and neither other table over this.

 

Bellow samples of my dataset and PBIX to make it more easy to understand.

Dataset

https://drive.google.com/open?id=1Mhg0QPpPxTPxmo_q2OYYwLn4xbiV47J1

PBIX

https://drive.google.com/open?id=1IPeK7j5uDWIU2517T8D_7l6Lh5nmCtmh

 

 

Thanks for your help.

Hi @WillianVictor 

you have exactly 12 Orders which connected with 2 different Situacaos (not sure how to write it correct 🙂

Of course, these orders are counted twice in chart - one for each Situacao

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi, @az38

 

I found this same answer too.

Actually, I've twelve orders been counted twice by different situations (Haha, Situação = Situation.. sorry about that, I sent to a BR Forum too).

But I was trying to do a distinct one that ignore this "situation" thing and only count all distinct orders independent where it is (where is the context).

 

I don't even know if this will work fully.. but I wanna give a try before use a new dataset without these double orders.

(The original dataset came from my company and run by invoice and not by orders, so if I've to change it.. it'll take a while. And I don't wanna it).

 

Anyway, thanks for your help!

Hi @WillianVictor ,

 

Here we go.

Measure 2 = 
VAR s =
    MAX ( Planilha1[Situação] )
VAR no =
    CALCULATETABLE (
        VALUES ( Planilha1[Ordens] ),
        FILTER ( ALL ( Planilha1 ), Planilha1[Situação] <> s )
    )
RETURN
    IF (
        ISFILTERED ( Planilha1[Situação] ),
        CALCULATE (
            DISTINCTCOUNT ( Planilha1[Ordens] ),
            FILTER ( Planilha1, NOT ( Planilha1[Ordens] IN no ) )
        ),
        DISTINCTCOUNT ( Planilha1[Ordens] )
    )

22222.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.