[HELP] Pie chart by distinctive category does not work

[GJE]

Hi everyone,

Let me start with an example table:

JobNumber	Time	Category
150	0,25	With time
300	0,065	With time
500	0,3	With time
580	1	With time
150	1	With time
300	0,25	With time
912	0,1	With time
300	0	Without time

As you can see in the JobNumber column I have some repeated values, currently I am interested in maintaining the table in this way but I want to make a pie chart showing the number of JobNumbers without repeating themselves, if they are repeated the time should be added to define the category (greater than 0 with time and less than or equal to 0 without time).

I have tried DISTINCTCOUNT for the JobNumber column for the pie chart values and the Category column for the categories but it doesn't work, the sum of the two categories ends up being greater than it should.

I also tried to create an identical table with only those values but since I have filters based on another column of that same table in the same dashboard, these stop working when I change the data source to the new table for the pie chart.

Any help will be welcome, thank you very much in advance.

[GJE]

Hi @v-huijiey-msft 

Once again, thank you very much! With that function for the calculated column, the pie chart worked perfectly, reflecting the real data. 😘

[v-huijiey-msft]

Hi @GJE ,

You cannot classify by one basis repeatedly and by another basis without repetition. You can only classify by one basis at the same time.

Create a measure:

Num = DISTINCTCOUNT('Table'[JobNumber])

This is classified according to Category:

This is classified according to Time:

This is classified according to JobNumber:

pbix is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GJE]

Hi @v-huijiey-msft ,

Thank you for your answer!

Initially this is what I'm looking for but what I don't understand is why if you add a card to which you add the metric, the result is 5 instead of 6. Shouldn't I get the same value? Basically this is my problem but with a much larger table where the difference looks like this:

Could it be that I am not understanding how DISTINCTCOUNT works and the result is correct?

Regards.-

[v-huijiey-msft]

Hi @GJE ,

Why should the result of Card be 6?

As shown in the sample data, there are 5 data "150 500 300 580 912". Drag Num into the card visual object, and the result is 5.

If you want to know more about DISTINCTCOUNT please see:

DISTINCTCOUNT function (DAX) - DAX | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GJE]

Hi @v-huijiey-msft 

okay now I understand.

One last question: could I make the pie chart a distinct count but generating the category dynamically based on the sum of time? That is to say, if I have 3 rows of JobNumber 300, add their times and categorize them based on that result.

Thank you very much again.

[v-huijiey-msft]

Hi @GJE ,

Like this?

Try placing the fields like this?

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GJE]

Hi @v-huijiey-msft 

For this particular case the result should only be "With time" since the add of Time for the different JobNumber 300 is higher then 0.

For example, if we had several records of the same jobnumber and all with time 0, the sum would be 0 and therefore it would be displayed under the category "Without time".

I hope I have brought a little more clarity to my problem.

[v-huijiey-msft]

Hi @GJE ,

Modify the measure syntax as follows:

Num = IF(MAX('Table'[Time]) <> 0, DISTINCTCOUNT('Table'[JobNumber]),0)

The page effect is as follows:

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GJE]

Hi @v-huijiey-msft 

Thanks again!

The following happens to me and I apologize for not mentioning before, the Category column is a conditional column whose function is the following:

TimeCategory = IF(DETAILOTS[TotalTheoreticalTimeHours] > 0, "With theoretical times", "Without theoretical times")

When applying the change you mentioned, the data in the pie chart does not fit completely. Is it due to this conditional column?

Regards.-

[v-huijiey-msft · Accepted solution]

Hi @GJE ,

This is the modified source data:

JobNumber	TotalTheoreticalTimeHours
150	25
300	65
500	3
580	1
150	1
300	25
912	1
300	0

According to the time classification of each Jobnumber, if the sum of Time of the same Jobnumber is >0, it is With theoretical times, otherwise it is Without theoretical times.

Create a calculated column:

TimeCategory =
VAR_IF =
     SUMX (
         FILTER (
             ALL ( 'DETAILOTS' ),
             'DETAILOTS'[JobNumber] = EARLIER ( 'DETAILOTS'[JobNumber] )
         ),
         [TotalTheoreticalTimeHours]
     )
RETURN
     IF ( _IF >= 0, "With theoretical times", "Without theoretical times" )

Create a measure for counting:

Num = DISTINCTCOUNT('DETAILOTS'[JobNumber])

The effect of the pie chart page is as follows:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!