cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

New Calculated Column dependent on one column and independent of all the other columns

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!

Solution Sage

Hi GeorgeGokmen,

Please check the below DAX measure for getting expected output.

NewMeasure =
Var job = 'Table'[Job]
Var denom = CALCULATE(SUM('Table'[b]),'Table'[Job]=job,ALLEXCEPT('Table','Table'[Job]))
Var result = DIVIDE('Table'[c],denom,0)
Return IF(ISBLANK(result),0,result)

Please refer to the below snapshot.

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,