Using a filter to then calculate a percentage

[PeterJMayes]

Hi. I am new to PowerBi but getting to grips with the Query Editor etc. I've been thrown in (I jumped really) the deep end with creating reports from our new Dynamics app and need to create a number of measures (all the same but with different variables).

We have an 'incident' table and can count the number of [incidentid]. In Card visuals I have filtered by a related table 'substatus' and picked different variables eg, In Progress, Completed. This has given me the totals. What I now want to do is create a Card that shows the % of the total number of incidentid that are In Progress. If there are 375 incidents in total (showing in an unfiltered card) and 75 in progress (showing in the filtered card); what is the DAX that would give me the result 20% (in a card).

I hopw that makes sense. PJ

[AlB · Accepted solution]

Hi @PeterJMayes 

You'd need to show some of your sample data, the fields in your tables, etc. to get an accurate answer.

Try something like:

Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( Table1[incidentid] ),
        SustatusTable[Status] = "Progress"
    ),
    CALCULATE (
        DISTINCTCOUNT ( Table1[incidentid] ),
        ALL ( SustatusTable[Status] )
    )
)

and format as %

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @PeterJMayes 

You'd need to show some of your sample data, the fields in your tables, etc. to get an accurate answer.

Try something like:

Measure =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( Table1[incidentid] ),
        SustatusTable[Status] = "Progress"
    ),
    CALCULATE (
        DISTINCTCOUNT ( Table1[incidentid] ),
        ALL ( SustatusTable[Status] )
    )
)

and format as %

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PeterJMayes]

Outstanding; looks like it has worked; and I even figured out how to use the FORMAT function 🙂

I was thinking about getting a tutor for the specific sort of questions I will likely have.

Thanks

PJ

[AlB]

@PeterJMayes 

I'm sending you a private message with regard to the tutoring/support.

Instead of using the FORMAT(), you can also set the format in the Measure tools menu. Select the measure, then Measure tools, then choose %:

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PeterJMayes · Accepted solution]

Ah ha

I thought there probably should be an easier way however my tab list only show the following:

It looks like I need to figure out how to get the Measures Tools option.

I said I was new to this 🙂

PJ

[PeterJMayes]

Sorted; didn't have the latest version with the new tools etc. 

Thanks for your help.

PJ