Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jocampo
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 :

jocampo_1-1657214870272.png

 

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:

 

jocampo_3-1657215426429.png

 

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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.

Thanks  a lot, it works perfectly. 😀

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors