Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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.
Step 4) create the following measure (It finds Min value in annual cost and multiple with user count like(2*10)
step5) create new measure for Annua cost saving
I hope I answered your question please give kudos and accept it as a solution!
Thanks
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.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
Step 4) create the following measure (It finds Min value in annual cost and multiple with user count like(2*10)
step5) create new measure for Annua cost saving
I hope I answered your question please give kudos and accept it as a solution!
Thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |