Forum Discussion

JonJon's avatar
JonJon
Frequent Visitor
7 years ago

Mix adusted Throughput calculation

Hi guys

 

I am new to Power Bi and cannot figure how I would do this:

 

I like to calculate the mix adjusted units per hour of Site A vs Site B. How productive would site A be if it had the product mix of site B

 

I have the following data datasets

 

Site|Week|Product |Units|Seconds

 

I would like to summarize the tables up which I managed to via

 

Mix_Adjustment = CALCULATETABLE(ADDCOLUMNS
(SUMMARIZE(
Table,
Table[Product],
Table[Site]
 
),
"Seconds",CALCULATE(SUM(Table[totalseconds])),
"Packages",CALCULATE(SUM(Table[total units]))
),
)
 
There are two things i like to do here a) give the user the option to filter this table by their home site
b) select a comparison site . The table then (somehow) needs to do the following calculation:
 
total units Site A * percentage per product from Site B * Units per Hour Site A
Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi JonJon 

    Assume you have such data

    site week product units seconds
    a 1 1 10 720
    a 2 1 20 1440
    a 3 1 20 720
    a 4 1 10 900
    a 5 1 5 360
    b 1 1 10 720
    b 2 1 20 1800
    b 3 1 20 720
    b 4 1 10 360
    b 5 1 5 360

     

    Would you like the calculation:

    (total hours every site, product)/hours

     

     "the option to filter this table by their home site", home site refer to "a","b" in this table, right?

     

    If so, create measures

    units total = CALCULATE(SUM(Sheet10[units]),ALLEXCEPT(Sheet10,Sheet10[site],Sheet10[product]))

total hours = CALCULATE(SUM(Sheet10[seconds]),ALLEXCEPT(Sheet10,Sheet10[site],Sheet10[product]))/3600

total/hour = [units total]/[total hours]

     

    If your scenario is not as this, please give some examples here.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JonJon's avatar
      JonJon
      Frequent Visitor
      7 years ago

      Hi v-juanli-msft 

       

      yes that's pretty much what i am after with one twist.

       

      So Site A processes Product 1 at 23 units per hour and Site B at 43 UPH.

      In my dataset i have many products 1- 30

       

      So lets assume that Site A processes Product 2 at 150 UPH and Site B at 155 UPH.

       

      However the mix is 20% Product 1 and 80% Product 2 for Site A but 50:50 for Site B.

       

      So whilst Site B is more productive per product overall they will show as less productive as they have more mix of the slower product.

       

      I like to mix adjust their overall rate. So the calculation needs to be

       

      UPH = (total units Site A * % product Site B ) / hours per Product A

       

      Although I suppose I could just take the volume of Site B and times it by the rates of A and ignore the % mix?

       

      Would that be best done in measure or in two seperate tables or both?

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi JonJon 

        I'm not very clear about your calculation

        UPH = (total units Site A * % product Site B ) / hours per Product A

        Could you use the example data we talked above to explain this.


        Best Regards
        Maggie