Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
GeorgeGokmen
Helper I
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

abcabc1vendor1o1001/1/2023
abcabc1vendor1c501/1/2023
abcabc1vendor2o551/1/2023
abcabc1vendor2c52/2/2023
abcabc1bbab10001/31/2023
abcabc2vendor2o252/2/2023
abcabc2vendor2c2002/3/2023
abcabc2bbcb600002/2/2023


I am trying to calculate a new calculated percentage measurement as seen below:



Sum of Amount  

Type      
CompanyJobSourcebcoNew Measure Calculation formula in ExcelCalculation steps
abcabc1bba1000  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%
abcabc1vendor1 501005.000000%  50 divided by sum of "amount" of "b" per "job" which  is 1000 equals to 5.000%
abcabc1vendor2 5550.500000%  5 divided by sum of "amount" of "b" per "job" which  is 1000 equals to 0.500%
abcabc2bbc60000  0.000000%  0 divided by sum of "amount" of "b" per "job" which  is 6000 equals to 0.000%
abcabc2vendor2 200250.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!

1 REPLY 1
nvprasad
Solution Sage
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. 

 

nvprasad_0-1685997236855.png

 

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

Regards,
N V Durga Prasad

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.