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 community,
I have a very simple question but all the answers that I get from AI either don't work because Power Bi is not eligible to use the new measure in the formula or results are not accurate. Here is the problem:
I have a data set as follows:
CompanyJobSourceTypeAmountDate
abc | abc1 | vendor1 | o | 100 | 1/1/2023 |
abc | abc1 | vendor1 | c | 50 | 1/1/2023 |
abc | abc1 | vendor2 | o | 55 | 1/1/2023 |
abc | abc1 | vendor2 | c | 5 | 2/2/2023 |
abc | abc1 | bba | b | 1000 | 1/31/2023 |
abc | abc2 | vendor2 | o | 25 | 2/2/2023 |
abc | abc2 | vendor2 | c | 200 | 2/3/2023 |
abc | abc2 | bbc | b | 60000 | 2/2/2023 |
I am trying to calculate a new calculated percentage measurement as seen below:
Sum of Amount
Type | |||||||||
Company | Job | Source | b | c | o | New Measure | Calculation formula in Excel | Calculation steps | |
abc | abc1 | bba | 1000 | 0.000000% | =E3/SUMIFS($D$3:$D$7,$B$3:$B$7,B3) | 0 divided by sum of "amount" of "b" per "job" which is 1000 equals to 0.000% | |||
abc | abc1 | vendor1 | 50 | 100 | 5.000000% | 50 divided by sum of "amount" of "b" per "job" which is 1000 equals to 5.000% | |||
abc | abc1 | vendor2 | 5 | 55 | 0.500000% | 5 divided by sum of "amount" of "b" per "job" which is 1000 equals to 0.500% | |||
abc | abc2 | bbc | 60000 | 0.000000% | 0 divided by sum of "amount" of "b" per "job" which is 6000 equals to 0.000% | ||||
abc | abc2 | vendor2 | 200 | 25 | 0.333333% | 200 divided by sum of "amount" of "b" per "job" which is 6000 equals to 0.333% |
Excel pivot can easily aggregate the table and can calculate the new measure as seen above but how can I create a Power Bi formula to calculate this new measure?
Data tabe and excel solution is in this link: https://judlauent-my.sharepoint.com/:x:/g/personal/goktem_ipcrp_com/Eb5_Ncyk5UhNuA3fxHRim8sBHB4k9Y8D...
Thank you all!
Hi GeorgeGokmen,
Please check the below DAX measure for getting expected output.
Please refer to the below snapshot.
Appreciate a Kudos!
If this helps and resolves the issue, please mark it as a Solution!
Regards,
N V Durga Prasad
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 |
---|---|
114 | |
96 | |
90 | |
79 | |
68 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |