cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Operations for Total results

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.

1 ACCEPTED SOLUTION
Super User

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

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.

2 REPLIES 2
Super User

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

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.

New Member

Thanks  a lot, it works perfectly. 😀

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors