Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!