Forum Discussion

VulcanPromance's avatar
VulcanPromance
Helper II
7 years ago
Solved

Calculate issue

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

Employees contain

IDNameCompanyCountry
1EricTest1 IncNorway
2PeterTest2 CorpSweden
3JohnTest3 ASDenmark


License contain

IDLicense Type
1Helpdesk
2Helpdesk
3Workorder
1Reception
2Workorder
3

Reception

 

And Cost contain

CategoryLicense TypeQuantity TypePrice
ServiceHelpdeskTotal425
ServiceReceptionpr license47
FacilityWorkorderpr license47


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.

CategoryLicense TypeQuantity TypePriceCountMonthly
ServiceHelpdeskTotal4252425
FacilityWorkorderpr license47294


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.
 
Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    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 

     

2 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    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 

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.