Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Divide in Power BI

‎07-23-2020 06:32 PM

Hi Everyone,

 

I am new to Power BI and DAX funcation, I have making report on power BI and I have Load Id number which I used as count function in PB so see how many load I have. Each load have diffrent number but many load number reapeat therefore I used count fucntion to know which stop I have how many load. Now the problem is when I use divide funtion in power BI I get wrong aswer, my percent give me wrong number. I want to make % of OTAS. please see bellow.

% OTAW = 'Data (2)'[OTAW]/'Data (2)'[Stop No]
I used devide fucntion too but same problem. Stop No are count function.
My result must be like this excel chart but on Power BI is diffrent. If any one know please advice.
Thanks
 
 
  3449  63%  
15467 29164786 53%88%
24030 17513171 43%79%
31638 6111237 37%76%
4594 164422 28%71%
5227 44136 19%60%
681 1232 15%40%
721 29 10%43%
83 01 0%33%
        

 

Labels:
Message 1 of 11
9,697 Views
0
1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

‎07-27-2020 12:13 AM

Hi @FarhanAhmed ,

 

135.PNG

 

 

 

 

 

 

 

 

For the data you share, you may create two measures instead of calculated column like DAX below.

 

% OTAS = DIVIDE(SUM('Data (2)'[OTAS] ), SUM('Data (2)'[Loads] ))



% OTAW = DIVIDE(SUM('Data (2)'[OTAW] ), SUM('Data (2)'[Loads] ))

 

Then you may choose Clustered column chart to display result, put [Stop] into Axis box of chart, put the new created measures [% OTAS] and % OTAW[] into Values box.

 

Note that the first link you shared can't be opened, and the [Loads] can't be found in your second pbix link, while it appears in forum shared data above, so does it really exist in your data table?

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Message 10 of 11
9,584 Views
0
10 REPLIES 10
v-xicai
Community Support
Community Support

‎07-27-2020 12:13 AM

Hi @FarhanAhmed ,

 

135.PNG

 

 

 

 

 

 

 

 

For the data you share, you may create two measures instead of calculated column like DAX below.

 

% OTAS = DIVIDE(SUM('Data (2)'[OTAS] ), SUM('Data (2)'[Loads] ))



% OTAW = DIVIDE(SUM('Data (2)'[OTAW] ), SUM('Data (2)'[Loads] ))

 

Then you may choose Clustered column chart to display result, put [Stop] into Axis box of chart, put the new created measures [% OTAS] and % OTAW[] into Values box.

 

Note that the first link you shared can't be opened, and the [Loads] can't be found in your second pbix link, while it appears in forum shared data above, so does it really exist in your data table?

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 10 of 11
9,585 Views
0
Anonymous
Not applicable
In response to v-xicai

‎07-27-2020 06:08 AM

Hi,

I got the solution for this question. Yes I had created two measure.

Thanks for your help.

 

Message 11 of 11
9,442 Views
0
FarhanAhmed
Community Champion
Community Champion

‎07-23-2020 08:24 PM

Can you please share the sample data with column headers in it ?

Also screenshot would be nice where problem occurs and what number you are expecting..

 







Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Proud to be a Super User!




Message 6 of 11
9,643 Views
0
Anonymous
Not applicable
In response to FarhanAhmed

‎07-23-2020 08:46 PM

Hi Farhan,

Are you able to see my data file?

Stop NoBOL countBOLWeekLoad IdStop #Stop TypeStop Window CloseScheduled ArrivalScheduled DepartureScheduled DurationActual ArrivalActual DepartureActual DurationLate Arrival Reason CodeDispatch Time Depart Gate TimeActual Gate TimeDayOTDOTA to TMS scheduled OTA to Window
01Y4810020193011PK2019-12-01 05:042019-12-01 00:312019-12-01 01:013.132019-11-29 21:222019-11-30 23:4426.4SG - System Generated2019-12-01 00:312019-12-01 01:012019-11-30 23:441Y  
10N010020193012DL2019-12-01 05:052019-12-01 02:052019-12-01 02:353.132019-12-01 00:492019-12-02 10:2533.6SG - System Generated     YY
01Y4910020203081PK2019-12-01 09:592019-12-01 02:282019-12-01 02:587.862019-11-30 13:252019-12-01 02:2913.1SG - System Generated2019-12-01 02:282019-12-01 02:582019-12-01 02:291Y  
11Y010020203082DL2019-12-01 07:002019-12-01 05:302019-12-01 06:037.862019-12-01 04:512019-12-01 05:380.8SG - System Generated     YY
21Y010020203083DL2019-12-01 08:002019-12-01 06:232019-12-01 07:037.862019-12-01 05:542019-12-01 06:541SG - System Generated     YY
30N010020203084DL2019-12-01 10:002019-12-01 08:002019-12-01 08:347.862019-12-01 07:542019-12-01 08:360.7SG - System Generated     YY
01Y4910020203121PK2019-12-01 12:292019-12-01 03:212019-12-01 03:5110.42019-12-01 13:272019-12-01 02:23 <Enter Reason Code>2019-12-01 03:212019-12-01 03:512019-12-01 02:231Y  
11Y010020203122DL2019-12-01 10:002019-12-01 07:002019-12-01 07:3610.42019-12-01 05:302019-12-01 06:301NS - Normal Status     YY
21Y010020203123DL2019-12-01 11:002019-12-01 08:032019-12-01 08:3810.42019-12-01 07:052019-12-01 07:530.8SG - System Generated     YY
30N010020203124DL2019-12-01 12:302019-12-01 10:362019-12-01 11:2910.42019-12-01 09:502019-12-01 10:581.1SG - System Generated     YY
01Y4910020203201PK2019-12-01 08:592019-12-01 03:282019-12-01 03:584.592019-11-30 14:272019-12-01 03:2913SG - System Generated2019-12-01 03:282019-12-01 03:582019-12-01 03:291Y  
11Y010020203202DL2019-12-01 08:002019-12-01 05:002019-12-01 06:064.592019-12-01 04:292019-12-01 05:130.7SG - System Generated     YY
20N010020203203DL2019-12-01 09:002019-12-01 06:252019-12-01 07:034.592019-12-01 05:412019-12-01 06:330.9SG - System Generated     YY
01Y4910020198661PK2019-12-01 09:592019-12-01 03:552019-12-01 04:255.712019-12-01 13:102019-12-01 04:38 <Enter Reason Code>2019-12-01 03:552019-12-01 04:252019-12-01 04:381N  
10N010020198662DL2019-12-01 08:002019-12-01 06:002019-12-01 06:455.712019-12-01 06:532019-12-01 08:011.1SG - System Generated     NY
Message 8 of 11
9,625 Views
0
Anonymous
Not applicable
In response to Anonymous

‎07-23-2020 08:58 PM

I am sending you sample PB file too.

 

https://stuconestogacon-my.sharepoint.com/:u:/g/personal/aamjad8961_conestogac_on_ca/Ed26shMTiXdAum5...

 

Message 9 of 11
9,620 Views
0
Anonymous
Not applicable
In response to FarhanAhmed

‎07-23-2020 08:35 PM

Hi Farhan,

Please see sample data excel onedrive link and my current PB status. I am looking to make graph similer to my screen short attached bellow. I also past excel summry work which I want to make on PB and want to create graph base on this summery.

I am new to power BI and new to this forum if you need any futher detail please ask me.

Thanks

 

 

https://stuconestogacon-my.sharepoint.com/:f:/g/personal/aamjad8961_conestogac_on_ca/EqCyQrybrRxGu1R...

 

Amjad1234_1-1595561507458.png

StopLoads OTASOTAW  OTAS %OTAW %
        
15467 29164786 53%88%
24030 17513171 43%79%
31638 6111237 37%76%
4594 164422 28%71%
5227 44136 19%60%
681 1232 15%40%
721 29 10%43%
83 01 0%33%

 

Message 7 of 11
9,627 Views
0
Greg_Deckler
Super User
Super User

‎07-23-2020 08:15 PM

@Anonymous - Hard to tell what is going on from the post. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Message 4 of 11
9,648 Views
0
Anonymous
Not applicable
In response to Greg_Deckler

‎07-23-2020 08:21 PM

Thanks,

It was my first ever post here but thanks.

 

 

Message 5 of 11
9,645 Views
0
amitchandak
Super User
Super User

‎07-23-2020 07:24 PM

@Anonymous , Divide should always be sum(A)/Sum(B). Sum can be count or other aggregation as needed.

 

% OTAW = divide(Sum('Data (2)'[OTAW]),sum('Data (2)'[Stop No])) //not sure if count is need in one of two

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 11
9,659 Views
0
Anonymous
Not applicable
In response to amitchandak

‎07-23-2020 07:37 PM

I treied this one earlier but it does not work. I tried again once you mentioned but still not correct answer. 

Column = DIVIDE(sum('Data (2)'[OTAS]),sum('Data (2)'[Stop No]))
I belive I need to add another colomn.
 
Message 3 of 11
9,652 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All