Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
RubySmith
Frequent Visitor

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

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

CompanyUserLicense
Co-1User 1 License 1

Co-1

User 2

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

 

Supplementary Table

LicenseAnnual 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

CompanyUserLicenseAnnual Cost
Co-1User 1 License 1$50

Co-1

User 2

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

 

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

CompanyCount of UserSum of Annual Cost

Co-1

2$70
Co-21$50
Co-31$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 

CompanyCount of UserSum of Annual CostSum of Downgrade Annual CostCost Savings

Co-1

2$70$20 (2 users x $10)$60
Co-21$50$10 (1 user x $10)$40
Co-31$10$10 (1 user x $10)$0

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

Thanks in advance!

1 ACCEPTED SOLUTION
Uzi2019
Super User
Super User

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 

Uzi2019_0-1697695952524.png

 

 steps2 ) create relationship between 2 tables based on license.

Uzi2019_1-1697696021513.png

 

Add cost to the Main table:

Uzi2019_2-1697696145285.png

 

 

step3) create measure under Main table for user count.

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

 

Uzi2019_3-1697696236406.png

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
 
Uzi2019_4-1697696370259.png

step5) create new measure for Annua cost saving

 

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

 

Uzi2019_5-1697696519592.png



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

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

View solution in original post

5 REPLIES 5
RubySmith
Frequent Visitor

thanks @Uzi2019 ! that worked 🙂 appreciate all your assistance

RubySmith
Frequent Visitor

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!

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
Don't forget to give thumbs up and accept this as a solution if it helped you!!!
ThxAlot
Super User
Super User

License Fee.pbix

 

ThxAlot_0-1697717235477.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Uzi2019
Super User
Super User

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 

Uzi2019_0-1697695952524.png

 

 steps2 ) create relationship between 2 tables based on license.

Uzi2019_1-1697696021513.png

 

Add cost to the Main table:

Uzi2019_2-1697696145285.png

 

 

step3) create measure under Main table for user count.

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

 

Uzi2019_3-1697696236406.png

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
 
Uzi2019_4-1697696370259.png

step5) create new measure for Annua cost saving

 

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

 

Uzi2019_5-1697696519592.png



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

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.