Operations for Total results

[jocampo]

Hello all,

I have a question about how to do this, I have a database with daily reports of the result, I have done a lot of summarized tables in order to show Monthly and weekly reports but I can´t show the result we need. This is the situation I have:

This is an example of a daily report :

In this case the Score is a calculated field with the relation between Actual vs Plan. (Score = Acual/plan x 100%) 

What I need is if I plot by date, or month or Office, the total Score must be calculated the same way and not the Average of all scores.

For example, the difference between the average of all Scores on the table and the calculation of the Score is:

At this moment if I want to calculate the Weekly score report by office I need to create a  summarized table form the original by "week" and "office" including the Sum( ) of all the actual and then the Sum( ) of all the plan, and then on this summarized table I create another "Score" calculated field and it works but I need to do this for each all the combinations: monthly report by office, Yearly report by seller, Weekly report region and Product, weekly by plant, etc. 

Also if I create a summarized table for each combination I'm not able to create a multi level reports where I can do a drill down from year to month, week or day or drill down from Region to plant, office and seller.

So, Is there a way that the calculated field "Score" is also calculated for the total? "Score" must be calculated according to the dynamic filters selected on the Power BI report.

Or maybe there's another and more clever way to do it.

 Thanks for your support

[AlexisOlson · Accepted solution]

This is a job for a DAX measure, not Power Query (or a DAX calculated column). More detail here:

https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not

If you define a measure like this:

DIVIDE ( SUM ( Table1[Actual] ), SUM ( Table1[Plan] ) )

Then it should work at whatever aggregated level you show in your visual.

[jocampo]

Thanks  a lot, it works perfectly. 😀