How to sum multiple categories of the same variables

[cesarllebron_1]

Hi

I'm trying to sum the amount of production per shift per machine.    A single machine could have multiples production in the same shift due to multiple change over.

I'm trying to work with SUMX to have a consolidated sum per machine as example machine A09, in 3/1/2022 3rd shift produced a total of 5,336 units.    But all the formula returned the same production 385,740,741....

Please what would be the right formula and/or ?   Thanks

[Whitewater100]

Hello:

Please see file for solution for combining Date, Machine & Shift in an aggregation measure. You will see there is a preliminary measure for total production which is used in final measure. I will attach file for your convenience. The solution can be two different ways.  I named your data table "data. I added date table so you have more flexibility to see other time periods.

https://drive.google.com/file/d/1EkdopX-6MeF-LqzOJuCavzHMNFhR2RzP/view?usp=sharing 

Total Production = SUM(Data[Produccion])

Machine & Shift Production = CALCULATE(

[Total Production],

ALLEXCEPT(Data, Data[Maquina],Data[Turno]))

SUMX Machine & Shift 2 = CALCULATE(SUMX(Data, Data[Produccion]),

ALLEXCEPT(Data, Data[Maquina], Data[Turno]))

*Note you could add this to last line of code if you wanted always just for a day at a time.(not likely)

SUMX Machine & Shift 3 = CALCULATE(SUMX(Data, Data[Produccion]),

ALLEXCEPT(Data, Data[Maquina], Data[Turno], Data[Fetcha]))

[cesarllebron_1]

Greetings

Thanks for your help.   Its is possible to have the solution in a calculated column instead of measure.

I would like to have a calculated columns that sum the production by shift, as below example.    If not problem if repeats the same value in the same shift.

Could you provide the right formula for this?   I was trying to use If and SUM but not succesful.

Thanks Again!!!!

[cesarllebron_1]

Hello,

Thanks again for your help.    

I did the formula, but noticed in the same date, 3rd shift had two machines working, A07 and A09.  The sum add all 3rd shift production for all machines.   For machine A07 should be 500 as total production.

To figure if I can solve the issue, I add machines as filter, but I getting this error

Thanks again, I wait for your response

[Whitewater100 · Accepted solution]

Hi:

I apologize for being late. I was tested positive for Covid and not feeling great.

This Calculated column seems to be working?

Machine & Shift by Day Prod = CALCULATE(

SUM(Data[Produccion]),

ALLEXCEPT(Data, Data[Turno], Data[Fetcha],Data[Maquina]),ALLEXCEPT(Dates,Dates[Date]))

Please find link with new dimension tables. I hope this is a good solution for you~

https://drive.google.com/file/d/1EkdopX-6MeF-LqzOJuCavzHMNFhR2RzP/view?usp=sharing 

[cesarllebron_1]

Good Morning

Sorry to hear that! I hope you are better and take care yourself.

Thanks for the solution, it works!

I just started to learn about Power Bi! and I need a lot of help!, I'm grateful for your support!

Thanks

Cesar L. Lebron

[cesarllebron_1]

Good Afternoon!

Any response?

[Whitewater100]

Hi:

Yes you can do this. Here is calculated column using the same table names as yesterday. I added more data to test. Please accept as solution if this works for you. Thanks

Machine & Shift by Day Prod = CALCULATE(

SUM(Data[Produccion]),

ALLEXCEPT(Data, Data[Turno], Data[Fetcha]))