cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Using a filter to then calculate a percentage

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

2 ACCEPTED SOLUTIONS
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

Ah ha

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

PeterJMayes_0-1604057756104.png

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

 

I said I was new to this 🙂

 

PJ

View solution in original post

5 REPLIES 5
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

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

@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 %:

AlB_0-1604055298806.png

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 

SU18_powerbi_badge

 

 

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 

 

SU18_powerbi_badge

Ah ha

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

PeterJMayes_0-1604057756104.png

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

 

I said I was new to this 🙂

 

PJ

View solution in original post

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

 

Thanks for your help.

 

PJ

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.