Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
4 years ago
Solved

Divide function not giving right value

Hi all,

 

in a tabel I have several measures displayed and in one of the measures i would like to calculate the margin% but it seems to give wrong value.

 

44/50=88%

any idea where i might be doing wrong? 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Probably a filter context issue...
    What is the code for both measures and 
    what does each measure in the DIVIDE function deliver in a card visual? 
    Also what does the % measure deliver for the total row if added to the table visual?

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    jalaomar ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    Try like

    Divide([Count of VSU fullfilment]*1.0, [Count Total])

    • jalaomar's avatar
      jalaomar
      Helper IV
      4 years ago

      Hi, These are my measures 

      VSU Days = DATEDIFF('KPI'[MIS3.1Actual 2];'KPI'[ITG4Actual2];DAY)

       

      VSU 5 weeks = IF('KPI'[VSU Days] <=35; "1";"0")

       

      PVoT Days = DATEDIFF('KPI'[ITG4Baseline2];'KPI'[ITG4Actual2];DAY)

       

      ITG4 fullfillmentX = SWITCH(TRUE();

      ISBLANK('KPI'[PVoT Days]);0;
      'KPI'[PVoT Days]>=1;0;
      'KPI'[PVoT Days]<=0;1;
      0)
       

      Count total = CALCULATE(COUNTROWS('KPI'))

       

      Count of VSU fullfillment = CALCULATE(COUNTROWS('KPI'); FILTER('KPI';'KPI'[VSU 5 weeks]="1"))
       
      calculate margin% = CALCULATE(DIVIDE('KPI'[Count of VSU fullfillment];'KPI'[Count total]))
       
      Was not able to provide sample data 
       
      i tried your measure and it gave me an error as shown in the screenshot 
      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Change the [Count of SVU fulfillment] to

        Count of VSU fullfillment = CALCULATE(COUNTROWS('KPI'); FILTER('KPI'; [VSU 5 weeks]=1))

         

        Also, please provide the answers to the following:

        what does each measure in the DIVIDE function deliver in a card visual? 
        Also what does the % measure deliver for the total row if added to the table visual?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Probably a filter context issue...
    What is the code for both measures and 
    what does each measure in the DIVIDE function deliver in a card visual? 
    Also what does the % measure deliver for the total row if added to the table visual?

    • jalaomar's avatar
      jalaomar
      Helper IV
      4 years ago

      Hi,

      I need to calculate how many projects that have fulfilled a certain criteria, all else is working well.

      These are my measures 

      VSU Days = DATEDIFF('KPI'[MIS3.1Actual 2];'KPI'[ITG4Actual2];DAY)

       

      VSU 5 weeks = IF('KPI'[VSU Days] <=35; "1";"0")

       

      PVoT Days = DATEDIFF('KPI'[ITG4Baseline2];'KPI'[ITG4Actual2];DAY)

       

      ITG4 fullfillmentX = SWITCH(TRUE();

      ISBLANK('KPI'[PVoT Days]);0;
      'KPI'[PVoT Days]>=1;0;
      'KPI'[PVoT Days]<=0;1;
      0)
       

      Count total = CALCULATE(COUNTROWS('KPI'))

       

      Count of VSU fullfillment = CALCULATE(COUNTROWS('KPI'); FILTER('KPI';'KPI'[VSU 5 weeks]="1"))
       
      calculate margin% = CALCULATE(DIVIDE('KPI'[Count of VSU fullfillment];'KPI'[Count total]))
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, jalaomar ;

    I tested a simple data, if your table's filter is different from your card visual , so it may be cause the different result.such as:

    1.when the table's filter is enddate is not 2021-2-1,the result is 4/5=0.8

    2.if the table not have filter .it show 0.67

    3.So keep the filter criteria consistent in every visual.

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.