Calculate issue

[VulcanPromance]

Ok.. So I have this project that involves 3 tables. Employees, Licenses, Cost

Employees contain

ID	Name	Company	Country
1	Eric	Test1 Inc	Norway
2	Peter	Test2 Corp	Sweden
3	John	Test3 AS	Denmark

License contain

ID	License Type
1	Helpdesk
2	Helpdesk
3	Workorder
1	Reception
2	Workorder
3	Reception

And Cost contain

Category	License Type	Quantity Type	Price
Service	Helpdesk	Total	425
Service	Reception	pr license	47
Facility	Workorder	pr license	47

I've created a custom column on Employee based on company to create Country.

The goal here is to be to click on a map, and get the amount of licenses for each country and show the cost pr license type monthly in .f.ex a table form like this.

Category	License Type	Quantity Type	Price	Count	Monthly
Service	Helpdesk	Total	425	2	425
Facility	Workorder	pr license	47	2	94

The count is a calculated column which goes to Licenses table to fetch distinct values

Count =

CALCULATE(

DISTINCTCOUNT(Licenses[ID]);

ALLEXCEPT(Cost;Cost[License Type])

)

Now when I click the country in the map it shows the correct license types in that country,
but it still show the count of the entire world of that particular license type.

And if I add the monthly cost column it shows cost for global license cost. Not for the country selected.

I tried to create a count based on measures

Count pr Country2 =

CALCULATE(

DISTINCTCOUNT(Licenses[ID]);

ALLEXCEPT(Employees;Employees[Country])

)

 Which made the count actually work. But Monthly costs still wont work. Even if I use the measure for calculating

Monthly Cost = IF(Cost[Quantity Type]="pr License";(Cost[Count pr Country2])*Cost[Price];(Cost[Price])*1)

Im pretty new to this stuff, and I cant seem to find any post here that lets me understand why this happens. Or how to fix it.. Hope someone can help.

[v-juanli-msft]

Hi @VulcanPromance 

Modify with AnthonyTilley's pbix, create measures instead of calculated columns (columns are static while measures are dynamic)

Count = 
CALCULATE(DISTINCTCOUNT(Licenses[License Type]),ALLSELECTED(Licenses))

Measure = 
Var ty = max(Cost[Quantity Type])
var co = count(Licenses[ID])
Var sw = SWITCH(ty,"TOTAL",sum(Cost[Price]),Sum(Cost[Price])*[Count])
Var bl = if(co = blank(),BLANK(),sw)
return bl

You can add [measure] is not blank in the visual level filter for the table visual, so final result can be

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnthonyTilley · Accepted solution]

i have created a PBIX file that may be what you want 

PBIX File

you dont not want to use a distinct count of the id becuase the id in itself is not unique. just use a count

this file has a sheet that alows you to do what you asked click on one of the countrys and the table to the right will show the brakedown of the number of licences by type with a count and a sum of the price based on the quantity type