PowerBI Percentage measure of total

[TimoOhio]

I have a dataset with 3 measures in :

1or2 = IF(ISBLANK ([SumOfInCompleted]),BLANK(),IF( [SumOfInCompleted] <3,1,0))

3or4 = IF(ISBLANK ([SumOfInCompleted]),BLANK(),IF(AND( [SumOfInCompleted] > 2 ,[SumOfInCompleted]<5),1,0))

5 = IF(ISBLANK ([SumOfInCompleted]),BLANK(),IF( [SumOfInCompleted] > 4,1,0))

basically, this adds a 1 if there are 1-2 jobs outstanding, 3-4, or 5+  .  This has helped to categorise my data, as below

Now I am looking to create something like below, green for 1or2, orange 3or4, red 5.

I am struggling to write a measure that converts the final values to percentages.  In the example data I would have a 25%,50%,25% splits as percentages of the totalcount.

I cannot get a percentage calculation to take a measure value.  I have previosuly used something like this: 

DIVIDE(SUM(vw_PBI_Results_Summarised[HasALLLearningToDo]),[CountOfStaff]) however this is using a fixed columnar value, rather than a dynamic value.

Can anyone help please?

[TimoOhio]

Thankyou @johnyip - just playing with those columns in the extra table as we speak "

VAR VirtualTable = SUMMARIZE(vw_PBI_Results, vw_PBI_Results[First Name], (((add here)), (((add here)), ((.........)), "SumOfInCompleted",[SumOfInCompleted], "1 or 2",[1or2]) You should include all the columns that you need to get filtered in the virtualtable, or the result will be inaccurate.", I will keep building on this.  Awesome result nevertheless.

[TimoOhio]

Thankyou for replying @johnyip , weird that your message isnt showing in the window.  Yes, the slicers run, Department and down to Team and then down to Person.  The data is showing as correct if there are none of the slicers sliced, OR if a staff member is selected, but does not calculate correctly in between these states - so the team as a whole or a department as a whole, UNLESS all of the staff are also selected in their slicer.  Is there a way to add more than 2 columns to the virtual table perhaps - ?

[johnyip]

Hi, is the [SumOfInCompleted] of your data a measure? If so, do you have a column like [Incomplete] in your data for you to count the incompleted task in your measure?

Also, can you provide us some sample data (can be fake) for reference? It can help a lot if you can provide the data strucrure. Most of the time it matters.

[johnyip]

Hi @TimoOhio, you can add more columns to the VirtualTable:

VAR VirtualTable = SUMMARIZE(vw_PBI_Results,
                             vw_PBI_Results[First Name],
                             (((add here)),
                             (((add here)),
                             ((.........)),
                             "SumOfInCompleted",[SumOfInCompleted],
                             "1 or 2",[1or2])

You should include all the columns that you need to get filtered in the virtualtable, or the result will be inaccurate.

[TimoOhio]

Thanks, so SumOfIncompleted is a measure 

CALCULATE(SUM(vw_PBI_Results[Is NOT Completed]),Filter(vw_PBI_Results,vw_PBI_Results[Is NOT Completed]=1)), which basically counts the number of rows where [Is Not Completed] = 1

 The data looks like this for example:

Thankyou

[johnyip]

Hi @TimoOhio ,

I think maybe the below might help:

Percentage_1 or 2 = 
VAR VirtualTable = SUMMARIZE(vw_PBI_Results,
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "1 or 2",[1or2])

VAR Count_1_or_2 = SUMX(VirtualTable,[1 or 2])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_1_or_2/Usercount
-------------------------------------------------------------------------------
Percentage_3 or 4 = 
VAR VirtualTable = SUMMARIZE(vw_PBI_Results,
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "3 or 4",[3or4])

VAR Count_3_or_4 = SUMX(VirtualTable,[3 or 4])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_3_or_4/Usercount
-------------------------------------------------------------------------------
Percentage_5 up = 
VAR VirtualTable = SUMMARIZE(vw_PBI_Results,
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "5 up",[5])

VAR Count_5_up = SUMX(VirtualTable,[5])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_5_up/Usercount

[TimoOhio]

Thankyou @johnyip  - this is awesome, and I would NEVER have come up with this.  It works perfectly when there are no filters on the page 

However, when there is a slicer on the page, then the data represented only shows what WOULD have been allocated to that team in regards the entire list, not recalculated for the now limited dataset, like below: 

The values are correct when viewed against the whole list, but once the dataset is sliced, the values should be recalculated for the data that is then available.  Would you know how to enhance the measures to be able to reflect the changes in the data please?  Hopefully you understand where I am coming from.

Thankyou for your help so far.

[johnyip · Accepted solution]

Please try the following, and I see that your sample used [Team Name] instead of [First Name].
I will treat both of them as the same in the following DAX. If they are different, add vw_PBI_Results[Team Name] in SUMMARIZE() after vw_PBI_Results[First Name].

Percentage_1 or 2 = 
VAR VirtualTable = SUMMARIZE(ALLSELECTED(vw_PBI_Results),
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "1 or 2",[1or2])

VAR Count_1_or_2 = SUMX(VirtualTable,[1 or 2])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_1_or_2/Usercount
-------------------------------------------------------------------------------
Percentage_3 or 4 = 
VAR VirtualTable = SUMMARIZE(ALLSELECTED(vw_PBI_Results),
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "3 or 4",[3or4])

VAR Count_3_or_4 = SUMX(VirtualTable,[3 or 4])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_3_or_4/Usercount
-------------------------------------------------------------------------------
Percentage_5 up = 
VAR VirtualTable = SUMMARIZE(ALLSELECTED(vw_PBI_Results),
                             vw_PBI_Results[First Name],
                             "SumOfInCompleted",[SumOfInCompleted],
                             "5 up",[5])

VAR Count_5_up = SUMX(VirtualTable,[5])
VAR Usercount = COUNTX(ALLSELECTED(vw_PBI_Results[First Name]),[First Name])
RETURN Count_5_up/Usercount

[TimoOhio]

Thankyou @johnyip , yes, I see the ALLSELECTED in the scripts.  I understand the logic now.  The selection of the team data works so long as there are team members selected - which is an awesome result.  Thankyou very much - I really appreciate the time you have spent.