topic How to sum multiple categories of the same variables in DAX Commands and Tips

How to sum multiple categories of the same variables

cesarllebron_1 — Tue, 26 Apr 2022 21:14:30 GMT

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

Re: How to sum multiple categories of the same variables

Whitewater100 — Wed, 27 Apr 2022 01:17:10 GMT

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

Re: How to sum multiple categories of the same variables

cesarllebron_1 — Thu, 28 Apr 2022 16:34:05 GMT

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!!!!

Re: How to sum multiple categories of the same variables

Whitewater100 — Fri, 29 Apr 2022 01:05:44 GMT

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

Re: How to sum multiple categories of the same variables

cesarllebron_1 — Fri, 29 Apr 2022 11:00:22 GMT

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

Re: How to sum multiple categories of the same variables

cesarllebron_1 — Mon, 02 May 2022 18:39:02 GMT

Good Afternoon!

Any response?

Re: How to sum multiple categories of the same variables

Whitewater100 — Mon, 02 May 2022 23:18:58 GMT

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

Re: How to sum multiple categories of the same variables

cesarllebron_1 — Tue, 03 May 2022 13:54:46 GMT

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