DISTINCTCOUNT values difference between AXIS visual and card visual

[Anonymous]

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])

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

[v-frfei-msft]

Hi @Anonymous ,

Here we go.

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

[Anonymous]

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]

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.

[Anonymous]

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.

[az38]

Hi @Anonymous 

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

[Anonymous]

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!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

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] )
    )

Pbix as attached.