Division Issue

[Anonymous]

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!

[v-alq-msft]

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]

@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]

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.

[Anonymous]

@v-alq-msft  Hi Allan,

thank you again, that works and I can apply it to other measures I need. I have two more questions (sorry!!! 😞 )

1. Is there any easy way to get my items in the same matrix with the correct result for those employees? If I create a new matrix it shows me the working days and number of customers for each employee but if I put the same items into the matrix with those measures it just show me the total number of working days and customers (red highlight)

2. Is it  possible to get the total (in this case the total average) of those measures (green)?

[v-alq-msft]

Hi, @Anonymous 

You may try the following measures to see if they work.

Sell-Out geteilt durch Einsatztage =
var t = ALLSELECTED('SO SOD monthly')
var tab = 
SUMMERIZE(
       'SO SOD monthly',
       'SO SOD monthly'[SOD],
       "Re",
       var _sod = [SOD]
       return
       DIVIDE (
           CALCULATE(
                   SUM ( 'SO SOD monthly'[Sell-Out] ),
                   FILTER(
                       t,  
                       'SO SOD monthly'[SOD]=_sod
                   )
          ),
          CALCULATE(
                  SUM ( 'SOD_Details'[Einsatztage] ),
                  FILTER(
                       t,  
                       'SOD_Details'[SOD]=_sod
                  ) 
          ) 
      )
)
return
SUMX(
   tab,
   [Re]
)

Kundenanzahi = 
var t = ALLSELECTED('SO SOD monthly')
return
SUMX(
   SUMMERIZE(
      'SO SOD monthly',
      'SO SOD monthly'[SOD],
      "Kundenanzahi",
      var _sod = [SOD]
      return
      CALCULATE(
                   SUM ( 'SO SOD monthly'[Sell-Out] ),
                   FILTER(
                       t,  
                       'SO SOD monthly'[SOD]=_sod
                   )
      )
  ),
  [Kundenanzahi]    
)

Einsatztage = 
var t = ALLSELECTED('SO SOD monthly')
return
SUMX(
   SUMMERIZE(
      'SO SOD monthly',
      'SO SOD monthly'[SOD],
      "Einsatztage",
      var _sod = [SOD]
      return
      CALCULATE(
                  SUM ( 'SOD_Details'[Einsatztage] ),
                  FILTER(
                       t,  
                       'SOD_Details'[SOD]=_sod
                  ) 
     )
  ),
  [Einsatztage]    
)

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]

Hello Allan,

@v-alq-msft 

I've tried to enter this formula (and changed "summerize" to "summarize" hope that's right) and then it gives me this notifaction:

A single value for column 'SOD' in table 'SOD_Details' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Maybe you can take a look at my file (and relationships). I am reallyy new to Power BI I kind of dont understand a word 😞

https://ufile.io/wh7smgmz 

Best,

Gabby

[westwrightj]

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]

@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]

@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 😞