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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |