Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Average per custom period

Hi there,

 

I want to display a average in a line diagram per given period.

 

My table contains order lines, order numbers (so one order number can contain multiple order lines) and the financial period (that does not match a calendar).

I need to display the orders per period (this is working with DISTINCTCOUNT of the order numbers), the order lines per month (COUNT order numbers) but also average of lines per order (=COUNT[OrderNo]/DISTINCTCOUNT[OrderNo]). If i build the average of lines per order i get the total average of the whole dataset, but i want to get it by financial period and display it in a line diagram.

Example of my dataset:

 

OrderNo|OrderLine|Period

900012|10|2018-2

900012|20|2018-2

900012|30|2018-2

900013|10|2018-2

900014|10|2018-3

900015|10|2018-3

 

In 2018-2 i have 2 orders, 900012 containing 3 lines, 900013 is 1 line. The average for 2018-2 is 4 lines/2 orders = 2 lines per order. In 2018-3 i have 2 order, one line each, so in total it is 1 line per order.

 

I'm new to powerbi, can you help me to put this into a measure?

 

Thank you.

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi Anonymous ,

    I created a measure you can have a try.

    Measure = 
var a = CALCULATE(DISTINCTCOUNT('Table'[OrderNo]),ALLEXCEPT('Table','Table'[Period]))
var b = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Period]))
return 
DIVIDE(b,a)

    Best Regards,

    Xue

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

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

    I created a measure you can have a try.

    Measure = 
var a = CALCULATE(DISTINCTCOUNT('Table'[OrderNo]),ALLEXCEPT('Table','Table'[Period]))
var b = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Period]))
return 
DIVIDE(b,a)

    Best Regards,

    Xue

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

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

    Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

     

    Best Regards,

    Xue

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