Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide in Power BI

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%
        

 

Tips and Tricks
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi FarhanAhmed ,

     


     

     

     

     

     

     

     

     

    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.

10 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.
       
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks,

      It was my first ever post here but thanks.

       

       

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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..

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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/EqCyQrybrRxGu1Rckn0kOsMBGIIVsrSRIpCevPviy4qKpw?e=O8Tdmv

       

      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%

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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
  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi FarhanAhmed ,

     


     

     

     

     

     

     

     

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

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

      Thanks for your help.