SUM per group, and then divide with the total

[Canadf]

I have a table like

Machine	time
cutter	10
cutter	20
cutter	30
weld	1
weld	2

Then I calculate the total of the time column, 63.

What I need, is to add the times grouping the machine, so cutter would have 60 minutes, and weld 3, and then I need to divide these two numbers against the total of the column time, giving me the ratio each one has.

Machine	Time	Ratio
cutter	60	0.95
weld	3	0.05

Ive been trying using Calculate and sum, but when I use sum in different measures, it just divides the total against the total so  get 1. 

I thought of the option of using a measure for each machine, but fro using a graph later it didn't work because there are more achines, and the measure doesn't relate to that machine

[v-yilong-msft]

Hi @Canadf ,

I create a table as you mentioned.

Then I create a measure and a calculated column.

Measure = SUM('Table'[time])

Column = SUM('Table'[time])

Finally I create another measure and you will get what you want.

Measure 2 = DIVIDE('Table'[Measure],MAX('Table'[Column]))

Best Regards

Yilong Zhou

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

[Canadf]

This almost worked, but the new "Column" is not being affecte by filters, how can I make it so the filters (I have one to filter the date and one to filter the machine) affect the sum column?

[v-yilong-msft]

Hi @Canadf ,

It seems that there is a date column in your data, can you show me more information about your table or give me your .pbix file?

Best Regards

Yilong Zhou

[AnalyticsWizard]

@Canadf 

It sounds like you want to create a ratio of each machine's time against the total time for all machines in Power BI. Here's a step-by-step way to achieve this using DAX:

1. First, create a measure to calculate the total time for all machines.

Total Time = SUM(Table[time])

2. Next, create a measure to calculate the total time per machine. This measure groups the time by each machine.

Machine Time = CALCULATE(SUM(Table[time]), ALLEXCEPT(Table, Table[Machine]))

3. Finally, create a measure to calculate the ratio of each machine's total time to the total time.

Time Ratio = DIVIDE([Machine Time], [Total Time])

With these measures, you can add these to a table visualization:
- Drag the `Machine` field and the `Machine Time` and `Time Ratio` measures into your table or chart.

This setup should dynamically calculate the time and ratios for all machines, and it will update as you add more machine types or data points. This avoids the need for creating individual measures for each machine, which as you've identified, can be limiting in graphical representations.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[mh2587]

Ratio Measure = // Try this
VAR TotalTime = SUM(Table[time])
VAR TotalTimePerMachine =
    SUMX(
        VALUES(Table[Machine]),
        CALCULATE(SUM(Table[time]))
    )
RETURN
    DIVIDE(TotalTimePerMachine, TotalTime)

[Canadf]

Thank you, bu it didn't work. It does the same as I tried, the ratio is 1 for evey machine, it's doin the sum per machine