0

I am looking to create a custom calculation for a visualization in Power BI. In particular, I am looking to get a weighted average of my data.

My data set looks something like this:

Cluster Name | Node | Call Name | Errors | Calls

I would like to make a dashboard where I'm giving the percent error over time based on filters on the level of Cluster Name, Node, Call Name, or some combination of these. (The cluster name being the most broad and the call name being the least broad.)

I can easily set up a SQL query that gives me the percent error for each of these categories by doing SUM(Errors)/Sum(Calls) grouped by the category used and this is what I would like to replicate in my visualization.

The reason why I cannot simply calculate the percent error for the broader categories by taking an average of the simpler categories is that not every Call Name has an equal number of Calls. Therefore, I have to use a weighted average or simply recalculate SUM(Errors)/Sum(Calls) for each category selection.

I have tried to accomplish this using a custom column using DAX, but the numbers that the column calculates make no sense. My formula is PercentError = DIVIDE(MyTable[Errors], MyTable[Calls],0)*100, but the calculation seems to give really off numbers. For example, one row has 45 errors and 48 calls, but the percent error is listed as 2630.

Is there a way to do this through the visualization and/or the custom column calculations?

Saralyn
  • 303
  • 2
  • 4
  • 9

1 Answers1

0

You can do this using a calculated measure rather than a calculated column. The formula would be exactly what you have described in your post

=SUM(Table[Errors])/SUM(Table[Calls])

Format as a % in the Modelling tab and voila.

Put your new measure in the values of a visual like a Matrix and put whatever columns you want to slice and dice this by in the Rows or Columns.

Jack
  • 3
  • 5