Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Division Issue

Hello it is me again,

 

I have an issue regarding a division calculation. I want the last column to be calculated from the total Sales ("Gesamt") and working days ("Einsatztage") all per employee. This is the formular for the Measure:

Sell-Out geteilt durch Einsatztage = 
DIVIDE(SUM('SO SOD monthly'[Sell-Out]), SUM('SOD_Details'[Einsatztage]))

What I want is "sales per day" but I do not understand what the results actually are. Maybe someone can help? I think thats kind of a very noob question - sorry!

 

Need Help

9 Replies

  • westwrightj's avatar
    westwrightj
    Resolver III
    6 years ago

    Hey Anonymous ,

     

        That is not a problem! Everyone has different levels of mastery with Power BI.

     

        I've input some test data to play with which looks like this

     

     

     


        In your current table it looks like you have the following

     

    Einsatztage - The number of days

    N Kunde - The number of customers

     

    Sell-Out Geteilt Durch Einsatzatage - This would be the average sell-out per number of days. So on average, for this particular SOD, this the sell-out amount per Einsatztage

     

     

    Hopfully that was helpful. I apologize if I misunderstood the original question - Let me know if that helped and if not feel free to send additional details.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      westwrightj 

      Thank you for your response. You totally understand the circumstances and the data.

       

      But as you can see in my screenshot e.g. "Cisse" has total sales of 2.984 and 12 working days ... - but the result is 24.223 which obviously can not be her sales per working day.Do you know what in my formula of the measure "So per Day" is wrong?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        westwrightj 

         

        As you can see below if I put the same measure but with different tables where I use "SOD" for the lines ... there are other results for the average sales per day 😞

         

         

         

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Anonymous 

     

    A measure operates on aggregations of data defined by the current context. You use the 'SOD' column from different tables to produce different context. The measure is calculated depend on the relationships between the table with 'SOD' column and the table with the aggregated column calculated in the measure, slicers or filters.

     

    You may try the following measures to see if it helps.

    Sell-Out geteilt durch Einsatztage =
var _sod = SELECTEDVALUE('SO SOD monthly'[SOD])
return
DIVIDE (
           CALCULATE(
                   SUM ( 'SO SOD monthly'[Sell-Out] ),
                   FILTER(
                       ALL('SO SOD monthly'),  
                       'SO SOD monthly'[SOD]=_sod
                   )
           ),
          CALCULATE(
                  SUM ( 'SOD_Details'[Einsatztage] ),
                  FILTER(
                       ALL('SOD_Details'),  
                       'SOD_Details'[SOD]=_sod
                  ) 
          ) 
       )

     

    Best Regards

    Allan

     

    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

      v-alq-msft 

       

      THANK YOU - the division is working now. What I additionally need is to filter with "month id" - with the sales it is already working, but with the division not yet. Is there something I have to add to your measure?

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Anonymous 

         

        You may modify the measure as below to see if it helps.

        Sell-Out geteilt durch Einsatztage =
var _sod = SELECTEDVALUE('SO SOD monthly'[SOD])
return
DIVIDE (
           CALCULATE(
                   SUM ( 'SO SOD monthly'[Sell-Out] ),
                   FILTER(
                       ALLSELECTED('SO SOD monthly'),  
                       'SO SOD monthly'[SOD]=_sod
                   )
           ),
          CALCULATE(
                  SUM ( 'SOD_Details'[Einsatztage] ),
                  FILTER(
                       ALLSELECTED('SOD_Details'),  
                       'SOD_Details'[SOD]=_sod
                  ) 
          ) 
       )

         

        Best Regards

        Allan

         

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