Counts of Distinct Users in bar chart is different that what is shown in Table visual

[darithear]

This is my dataset:

UserTrainingContent StatePercent Complete

Aamir S	Orientation	Assigned	0.00%
Aamir S	Simulation	Enrolled	0.00%
Aaron H	Orientation	In Progress	33.00%
Aaron H	Simulation	Enrolled	0.00%
Aaron L	Orientation	Completed	100.00%
Aaron L	Simulation	Completed	100.00%
Aaron S	Orientation	Completed	100.00%
Aaron S	Quick Start	Assigned	0.00%
Aaron S	Simulation	Completed	100.00%

I am using this calculated column to produce an overall status regardless of what is filtered in the Training column:

Status Column =

VAR UserTrainingAvgPercentComplete =

    CALCULATE(

        AVERAGE('Sample Data'[Percent Complete]),

        ALLEXCEPT('Sample Data','Sample Data'[Training], 'Sample Data'[User])

    )

RETURN

    SWITCH (

        TRUE(),

        UserTrainingAvgPercentComplete = 1, "Completed",

        UserTrainingAvgPercentComplete = 0, "Not Started",

        "In Progress"

    )

When I create a table with no filters applied the results are as expected:Table visual returns 4 users with an overall status

However, using a Pie Chart visual, the Distinct count of users total 6 when it should be 4.

However, in a pie chart visual. 6 users are counted with a Status.

The expected results in the bar chart should be what is displayed in the Table visual.  1 User is Completed, 2 Users is In Progress, and 1 User is Not Started.  Any advice here?

[jjrand]

Hello,

Unless I misunderstand what the other users mean, I think it makes sense what you're trying to calculate. I have a solution, though I couldn't fit it into a single measure. Perhaps someone else can improve on it, but meanwhile this should work.

First, I modified your measure a bit

Status - Measure =

VAR _StatusPercentage =

    AVERAGEX(

        'Sample Data',

        'Sample Data'[Percent Complete]

    )

VAR _Status =

    SWITCH(

        TRUE(),

        _StatusPercentage = 1, "Complete",

        _StatusPercentage = 0, "Not Started",

        "In Progress"

    )

RETURN

_Status

I then created a calculated table

Grouped Sample Data =

SUMMARIZECOLUMNS(

    'Sample Data'[User],

    "Status", [Status - Measure]

)
The output of which is

The table shows the overall status. Let's go one by one

For User A, all the courses are Not Started, so the overall is Not Started
For User B, some are in Progress, some are not, so the overall is In Progress
For User C, all the courses are Completed, so the overall is Completed

For User D, some are Completed, some are Not Started, so the overall is In Progress

I then wrote the following measure

User Count= DISTINCTCOUNT('Grouped Sample Data'[User])

Which does a distint count in a given filter context

I then put "Status" from the "Grouped Sample Data" table and "User Count" measure as columns in a table visual, just so we're clear what's going on.

To view it as a pie chart, I put "Status" as Legend, and "User Count" as Values.

I hope this answers your question.

[darithear]

Thanks for the replies.  How do I updated the Status Column to create only one Overall Status per User?

[TomMartens]

Hey @darithear ,

basically, this is simple: describe the business rule that defines the Overall Status per user. Use plain English no DAX, but make sure that the rule creates the expected outcome for all the users in your sample data. We are here to provide the DAX, but we need to know how the DAX should work.

Regards,

Tom

[darithear]

Hi Tom.  The overall status is based on Avg Percent Complete of all Training.  Training needs to be a context filter so that the overall status is a dynamic status based on the selection in that filter.  100% = Completed, 0% = Not Started, else if > 0% and < 100% THEN In Progress.

If the data is as is with no filter.  The overall status for Aamr S should be "Not Started" since his Avg % Completed between the Orientation and Simulation Training is 0%, for Aaron H his overall status should be "In Progress" between Orientation and Simulation Training., for Aaron L his overall status should be "Completed" because he completed both Orientation and Simulation, and for Aaron S his overall status should be "In Progress" because he completed Orientation and Simulation but did not complete Quick Start training.  The overall status needs to be dynamic so that if the user only selects one or multiple trainings, the overall status will reflect what is filtered.

[TomMartens · Accepted solution]

Hey @darithear ,

as a user can select one or more trainings a calculated column can not be used because a calculated column will only be evaluated during data refresh but not when a user selects a training. The number per status has to be counted by a measure.

For this it becomes necessary that you first create a table that contains the possible status values, like: Completed, In progress, and Not Started. This table is not related to the table you provided with your initial question.

You can use the below DAX statement to created this table, but it will be better if you create this table using Power Query:

Status = 
DATATABLE( "Status", STRING , { {"Completed"} , {"In progress"} , {"Not Started"} } )

Please keep in mind that in my solution the table you provided with your initial post is called 'Table', this means you must adapt the below DAX to your table name.

This is the measure that counts the status:

count status = 
 var currentStatus = SELECTEDVALUE( 'Status'[Status] )
 return
 SWITCH(
    currentStatus
    , "Completed"
        , COUNTX(
            VALUES( 'Table'[User] )
            , VAR _v = CALCULATE( AVERAGE('Table'[Percent Complete] ) )
            return IF( _v = 1 , 1 , BLANK() )
        )
    , "In progress"
        , COUNTX(
            VALUES( 'Table'[User] )
            , VAR _v = CALCULATE( AVERAGE('Table'[Percent Complete] ) )
            return IF( _v < 1 && _v > 0 , 1 , BLANK() )
        )
    , "Not started"
        , COUNTX(
            VALUES( 'Table'[User] )
            , VAR _v = CALCULATE( AVERAGE('Table'[Percent Complete] ) )
            return IF( _v = 0 , 1 , BLANK() )
        )
 )

A pie chart:

This solution is abstracted from this pattern: https://www.daxpatterns.com/static-segmentation/

The solution only requires a single measure, but need a Status table. But besides that, also your requirement regarding the filtering of trainings is considered by this solution.

Regards,

Tom

[darithear]

Thanks Tom for working on this solution and providing a reference.  I'll apply this concept to a larger dataset and see if it will yield the same results.  Many thanks!

[Ritaf1983]

Hi @darithear 
This happens because you have the same name with different statuses..

you need to decide what the logic to apply in this cases

[TomMartens]

Hey @darithear ,

you provide the DAX code for a calculated column: " ... this calculated column ..."

Adding a column to the data you provided creates this:

If you look closely, there are

• three distinct user with the status "Not started"
• two distinct users with the status "Completed" and 
• one user with the status "In Progress"

The pie chart is visualizing what's in the table.

I assume this is different from what you are expecting.

Please describe why you are expecting a count of 4 "distinct user."

Regards,

Tom