# of occurrences based on measure calculations

[CBurton87]

Hi all, I am trying to group sales people in to three groups. 

Green = Above budget

Amber = >85% of budget

Red = <85% of budget

I have a single table that holds actual and budget figures at a customer level, each customer line has the sales owner.

to start with, i have some basic measures.

Actuals $ = CALCULATE(sum('Sales Table'[USD_Amount]),filter('Sales Table','Sales Table'[Type]="Actual"))

Budget $ = CALCULATE(sum('Sales Table'[USD_Amount]),filter('Sales Table','Sales Table'[Type]="Budget"))

I then have a measure to calculate the budget % achieved

Budget % = divide([Actuals $],[Budget $],blank())

from this, i need to count, how many sales people are Green, Amber and Red based on the above logic.

any help would be appreciated.

Thanks.

[Greg_Deckler]

Measure = IF([Budget %] > 1,"Green",IF([Budget %]<.85,"Red","Amber"))

?

[CBurton87]

Thanks Greg, 

that would allow me to show in table visualisation who is Red, Amber and Green,

but in addition to that, i need to know how many are Red, how many are Amber and how many are Green.

sort of a distinct count of sales peoples names based on Red, Amber and Green.

thanks and

Best Regards,

[Greg_Deckler]

You can use SUMMARIZE, ADDCOLUMNS and such for that. Sample data and expected output would be really helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[CBurton87]

Here is a basic version of the data that should hopefully help.

Sales Table

Type	AccountKey	Month	Year	USD Amount	Sales Owner
Actual	M036249567	1	2018	716	Person 1
Actual	M036351101	1	2018	92	Person 2
Budget	M036249567	1	2018	785	Person 1
Budget	M036351101	1	2018	407	Person 2
Actual	M036249567	2	2018	930	Person 1
Actual	M036351101	2	2018	9	Person 2
Budget	M036249567	2	2018	507	Person 1
Budget	M036351101	2	2018	692	Person 2
Actual	M036249567	3	2018	956	Person 1
Actual	M036351101	3	2018	742	Person 2
Budget	M036249567	3	2018	77	Person 1
Budget	M036351101	3	2018	902	Person 2
Actual	M036249555	1	2018	945	Person 3
Actual	M036351589	1	2018	752	Person 4
Budget	M036249555	1	2018	836	Person 3
Budget	M036351589	1	2018	492	Person 4
Actual	M036249555	2	2018	862	Person 3
Actual	M036351589	2	2018	13	Person 4
Budget	M036249555	2	2018	983	Person 3
Budget	M036351589	2	2018	673	Person 4
Actual	M036249555	3	2018	874	Person 3
Actual	M036351589	3	2018	299	Person 4
Budget	M036249555	3	2018	879	Person 3
Budget	M036351589	3	2018	9	Person 4

the visualisations on the report allow the user to cut the data by month and year.

thanks and

Best Regards,

[Greg_Deckler · Accepted solution]

So, this is the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

In your case, creat this table, I call this Table19:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUYrViVZyL0pNzQOzHHOTUouUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}})
in
    #"Changed Type"

Create the Measure that I specified, I called this measure "Code".

Create this measure:

Count of Code = 
VAR __code = MAX('Table19'[Code])
VAR __tmpTable = SUMMARIZE('Table18',[Sales Owner],"__code",[Code])
RETURN COUNTROWS(FILTER(__tmpTable,[__code]=__code))

Put Code from Table19 into the Axis and Count of Code as the Value in a bar/column chart. Table18 is my Sales table.