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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tommpl
Regular Visitor

Created column isn't calculating as expected

I have two calculated columns from my dataset. I am trying to get a % figure based on these columns. As you can see from the picture below the columns calculate correctly and have been sorted by operatorname as I would hope but the % calculation hasn't worked. I would expect the abort % column to be 15/66 = 0.227 and so on but that's not what I'm seeing. Am I doing something wrong?

 

definition

14 REPLIES 14
balaganeshv2201
Frequent Visitor

try division(IRES[totalaborts],IRES[Totaloutcomes ])

Division didn't work. However DIVIDE did in that it resolved but it is still shows the incorrect figures.

 divide.png

 

 

 

Hello @tommpl,

 

Can you please share your sample data or sample pbix file?

I'm using directquery to get the data. Is it still possible to share that securely?

Can you recreate your sample data and share as an excel file?

https://ufile.io/up7a6 for the sample data

 

My calculated fields are

 

Total Aborts = if(ires[rhOutcome] = "ABORT",1,0)

 

Total Outcomes = if('ires'[rhOutcome] = "",0,1)

 

Abort % = DIVIDE('ires'[Total Aborts],'ires'[Total Outcomes])

 

Hope that's useful is diagnosing what I'm doing wrong 🙂

Hi @tommpl,

Based on my test, you could refer to below formula:

Abort % = DIVIDE(CALCULATE(SUM('ires'[Total Aborts])),CALCULATE(SUM('ires'[Total Outcomes])))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

I get this error message

 

Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models.

 

😞

Hello @tommpl,

 

Please create calculated columns as follows:

 

Total Aborts = if(ires[rhOutcome] = "ABORT",1,0)

Total Outcomes = if('ires'[rhOutcome] = "",0,1)

 

And create a calculated measure as follows:

Abort % = DIVIDE(SUM('ires'[Total Aborts]),SUM('ires'[Total Outcomes]))

 

Hope this helps.

error.jpg

 

 

Still getting the same error

Are using SUM in calculated measure or calculated column?

Hi @tommpl,

The formula I have created is a measure and the measure could work on Direct Query models, could you please try create it again?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @tommpl,

 

Do you want your expected result to be shown as below?

result.JPG

 

 

Please let me know if this is not what you want.

 

Regards.

Yes that is what I'm after.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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