Divide Sum of Values in Visual Table

[Justair07]

Hi,

This one really has me stumped 😞

I have two valuse I need to divide (RunHrs/DownHrs). Both valuse are sums from my data source. I tried something silly before I realized why it wouldn't work. I tried to add a custom column dividing the numbers in the query table. But obviously this won't work because the two valuse are sums and there is never a RunHrs and DownHrs on the same record so it's always divided by 0 or 0 is divied by a number.

This doesn't have to be displayed in a table, I figured it would be easier for me to display in a table.

Any help is much appreciated.

Thank you

[MFelix]

Hi @Justair07,

You just need to had the following measure to your model:

RunPerc = SUM(Table[RunHrs]) / SUM(Table[DownHrs])

This is based that your table has two different columns on for Run and another for Down.

If your table as a classification to make the difference between RunHrs and DownHrs you need to have something like this:

RunPerc = CALCULATE(SUM(Table[Hours]); Table[HoursType] = "RunHrs")/CALCULATE(SUM(Table[Hours]); Table[HoursType] = "DownHrs")

Change the name of the columns with the ones from your table.

If any of this examples doesn't fit your model please share the setup of your table to calculate the sum of hours.

Regards,

MFelix

[Justair07]

@MFelix

Thank you for the reply.  Your 2nd solution is more applicable, however I'm getting an error after each semi colon (see 1st image).

Just to take another shot, I created two custom columns, one for DownHrs and one for RunHrs then used your first solution. But the math isn't quite right (see 2nd image).

[Justair07]

@MFelix

The math isn't what I would have expected. In the 1st image you can see my attempt just to see if the math was adding up which it isn't. 12.98 / 11.02 = 1.18 and the number returning is 6.49.

In the 2nd and 3rd pictures you can see my results from the 2nd solution you suggested (comma instead of semi colon).

[MFelix · Accepted solution]

Hi @Justair07,

You are adding a column to your data table, you need to add a measure this will then be calculated based on context and return the value you need.

Regards,

MFelix

[Justair07]

@MFelix

Percection! Thank you sir.

[MFelix]

Replace the semicolon by a comma it has to do with regional settings my dax separstor is semicoloon.

Regards,
MFelix