New to DAX - Need help with percentage of total using count

[Anonymous]

Morning,

I have a report need that seems simple but I am unable to accomplish it with DAX.

The important information is that i have records with 4 important columns i'm trying to work with

ID

FunctionalDomain

AnticipatedRelease

Status

Example:

ID          FunctionalDomain              AnticipatedRelease              Status

1            B                                         8                                           5

2            B                                         8                                           4    

3            B                                         8                                           2 

4            B                                         8                                           5

5            C                                         8                                           5

6            C                                         8                                           1

7            C                                         8                                           1

8            C                                         8                                           2

There are many other FunctionalDomains and AnticipatedReleases

The main page has a slicer for Anticipated Release

i'm trying to put together gauges that shows % of items with status 5 (which is the final status) with each gauge per functional domain

Gauge 1 - Filtered to FunctionalDomain B that shows 50% (with the goal being 100%)

ie.  (Count of IDs with functionalDomain B with Status 5) / (Count of IDs with functionalDomain B with all statuses)

Gauge 1 - Filtered to FunctionalDomain C that shows 25% (with the goal being 100%)

ie.  (Count of IDs with functionalDomain C with Status 5) / (Count of IDs with functionalDomain C with all statuses)

etc

This seems so simple but i'm not sure what to use.  

Thank you in advance for any help you can provide

Sean

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Maybe you could try like this:

Measure = 
VAR STATU5 =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[FunctionalDomain], 'Table'[AnticipatedRelease] ),
            'Table'[Status] = 5
        )
    )
VAR ALLSTATU =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[FunctionalDomain], 'Table'[AnticipatedRelease] )
    )
RETURN
    DIVIDE ( STATU5, ALLSTATU )

I have attached the sample below. You could download it to have a try.

Best Regards,

Xue Ding

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

[Greg_Deckler]

First thing to try. In Fields area, click column/measure drop down arrow and see if Show as ... is available. You should have options like Percent of Grand Total, etc.

[Anonymous]

Hello,

Yes it does have it.  When i do % of Grand Total the value just stays 100% and won't change.  Maybe i'm just being an idiot....

See screen shot.