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 ID Name Company Country 1 Eric Test1 Inc Norway 2 Peter Test2 Corp Sweden 3 J...
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 

     

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.