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, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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