Overall Figure/Table/Graph by summary of Measure in DAX

[shaniwinfield]

Hi

I've been struggling with this for a while, and no amount of googling has got me any closer.

I have a dashboard which monitor people carrying out tasks on an app. If they get to the end, they are classed as 'Fully Compliant'.

I have a table on my dash that illustrates this at a user level, which works fine. (see below mocked up in dummy data), but regardless of how I've tried to do it, I can't get an overall figure. 

From the above the:

• User is a field in the table
• Orders counts the number of rows in the table for each user
• Fully Compliant = IF([App Used in Full] <> [Orders],"N","Y")
  [App used in full] counts completed rows in same table

The issue that I have is that the company want to have a summary of how many users are compliant, and have visual as numbers, graphs etc. See below mock up in Excel.

There are numerous date/category filters on the dashboard from that table, which still need to work for the 'Fully Compliant' field, which is why (unless you guys know a way) I can't use Power Query.

I'll obviously need to tie in the User to the formula, but I can't seem to group by the Y/N measure to do Countif etc. I've tried creating a table based on SUMMARIZECOLUMNS which also did not work.

Any and all help is much appreciated.

Thanks

Shani

[Icey · Accepted solution]

Hi @shaniwinfield ,

I create a sample, not the same but can be a reference. Please check:

1. Enter data to create a table: 

2. Create measures.

User Count 1 = 
COUNTROWS (
    FILTER (
        VALUES ( 'Table'[User] ),
        [Fully Compliant] = SELECTEDVALUE ( 'Fully Compliant Table'[Fully Compliant] )
    )
)

User Count 2 = 
SUMX ( VALUES ( 'Fully Compliant Table'[Fully Compliant] ), [User Count 1] )

3. Create visuals.

For more details, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shaniwinfield]

Hi @Icey 

That's absolutely brilliant, It worked perfectly, thank you so much. 

Shani