Is there a way to get / reference a specific table field / value in a measure

[RubySmith]

Hi, 

New to PowerBi, I have a Main Table with User data, as well as Current License. In a Supplemenatry table, I have a list of all Licenses and associated Annual Cost. 

Main Table

Company	User	License
Co-1	User 1	License 1
Co-1	User 2	License 2
Co-2	User 3	License 1
Co-3	User 4	License 3

Supplementary Table

License	Annual Cost
License 1	$50
License 2	$20
License 3	$10

In Query Editor I merged the tables to get Annual Cost onto the Main Table

Company	User	License	Annual Cost
Co-1	User 1	License 1	$50
Co-1	User 2	License 2	$20
Co-2	User 3	License 1	$50
Co-3	User 4	License 3	$10

In Power Bi, I have this data summarised and displayed in a Visual

Company	Count of User	Sum of Annual Cost
Co-1	2	$70
Co-2	1	$50
Co-3	1	$10

What I now need to do is show what the Annual Cost would be if all Users downgraded to the cheapest License (License 3). I don't know a way to do this other than going to the Main Table in Query Editor and adding a new column with the License 3 cost on every Row. 

Is there a way to do this in PowerBi directly? Can I create a variable (measure?) and set it to / reference the Annual Cost of License 3 specifically form the Supplementary Table, then create a new (measure?) of Count of Users x this License 3 Annual Cost variable? 

What ideally would be the final table 

Company	Count of User	Sum of Annual Cost	Sum of Downgrade Annual Cost	Cost Savings
Co-1	2	$70	$20 (2 users x $10)	$60
Co-2	1	$50	$10 (1 user x $10)	$40
Co-3	1	$10	$10 (1 user x $10)	$0

(sorry for table formatting, can't seem to change column width / borders???)

Thanks in advance!

[RubySmith]

thanks @Uzi2019 ! that worked 🙂 appreciate all your assistance

[RubySmith]

Thanks @Uzi2019 ! that is working for me.

One last question though, in step 4 is it possible to Return the Annual Cost for a specified License by the License name like a vLookup ("License 3") instead of using Min? 

In the posted example I needed to downgrade the users to the lowest license, but there are other pages with different filter conditions where I downgrade to different licenses e.g. from License 1 to License 2.

Is there a way to explicity say return the Annual Cost for "License 2", "License 3" etc?  

Thanks in advance!

[Uzi2019]

Hi @RubySmith 
Yes, You can create cost for different license.

License 1 = CALCULATE(SUM('Main(cost)'[Annual Cost]),'Main(cost)'[License]="License 1")

same for License 2 & 3.

I hope I answered your question.

Please give kudos and accept it as a solution!
Thank you

[ThxAlot]

License Fee.pbix

[Uzi2019 · Accepted solution]

Hi @RubySmith 
I created the same scenario. I am getting the result that you want. please do the following steps.

Step1-) Create 2 seperate table like in your case 

 steps2 ) create relationship between 2 tables based on license.

Add cost to the Main table:

step3) create measure under Main table for user count.

Count(user) = COUNT('Main (1)'[User])

Step 4) create the following measure  (It finds Min value in annual cost and multiple with user count like(2*10)

Min license =

var M= CALCULATE(MIN('Main(cost)'[Annual Cost]),ALLSELECTED('Main (1)'[Company]))

var N= [Count(user)]*M

Return N

step5) create new measure for Annua cost saving

Annual cost saving = sum('Main(cost)'[Annual Cost])- [Min license]

I hope I answered your question please give kudos and accept it as a solution!
Thanks