Correct Sub-Total and Total

[Shay A]

Hi,

I am trying to get the correct target sales for each sales manager. The target table is the one side and the sales table is the fact table.

The numbers in red are incorrect.


TY!

 

[Chihiro]

Avg calculation is correct as per your table.

If you want to show average as (11m + 7m)/2 for George... then you'd need to keep dimension (target table) and fact table separate, as well as adding another table for Sales Manager - Sales Rep dimension.

Then you'd use column(s) from dimension tables as row labels and aggregate data based on it.

As general rule of thumb, don't merge dimension to fact table (or don't add it as calculated column).

Edit: You could use complex DAX to to handle separate calculation for subtotal/total... but that kind of defeats the purpose of setting up data model for analysis.

 

[Chihiro]

Oh, as general practice, I'd recommend sticking to Star Schema model for data model. Especially when you are not used to data model and how DAX works.

See links for articles on the topic.
https://www.fenwick.com.au/blog/2017/10/25/introducing-star-schemas-for-power-bi/
https://businessintelligist.com/201...n-power-bi-and-power-pivot-using-power-query/
https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

 

[Shay A]

What do you think about this workaround?

 

[Chihiro]

My preference is to avoid calculated columns in fact table. I prefer to keep dimension table separate from fact table and use measures to aggregate data.

But if you are happy with your result and if no extra dimensions are needed for analysis. Go with it.