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

Frequent Visitor

## Incorrect subtotals in matrix visual

I'm calculating the run rate for sales managers to project how many onboarded customers have placed an order on an ecom app at the end of a period. The start of period (Date_initialised) for each sales manager is different but the end of period is the same. The DAX measure per sales manager is correct, but it won't aggregate to higher hierarchical levels (I have multiple hierarchical levels e.g., suburbs, regions) in the matrix visual. My DAX is as below:

``````CurrentRunRate =

VAR Last_update = MAX(Fact_Weekly[full_date])

VAR Date_initialised = SELECTVALUE(Fact_DateInitialised[Date Initialised])

VAR Days_gone = DATEDIFF(Date_initialised, Last_update, DAY)

VAR End_of_period = DATE(2023,11,2)

VAR Days_in_period = DATEDIFF(Date_initialised, End_of_period, DAY)

VAR Avg_per_day =
IF(
DATEDIFF(Last_update, End_of_period, DAY) > 0,
DIVIDE(
[Have Ordered],  /*<-- SUM(Dim_customer_detail[Have Ordered (1/0)])*/
Days_gone
)
)

Return
Avg_per_day * Days_in_period``````

I later replaced

``VAR Date_initialised = SELECTVALUE(Fact_DateInitialised[Date Initialised])``

with

``VAR Date_initialised = MAX(Fact_DateInitialised[Date Initialised])``

Although there are values in the subtotals and totals, they are incorrect (snip below, all the blues are subtotals). For example, I want 90+100=190 not 198.

1 ACCEPTED SOLUTION
Super User

@christinaxxx , Create a new measure on top of current measure

Sumx(Values(Table[Manager]), [CurrentRunRate])

2 REPLIES 2
Super User

@christinaxxx , Create a new measure on top of current measure

Sumx(Values(Table[Manager]), [CurrentRunRate])

Frequent Visitor

Brilliant! Thank you so much! I did try this but I created a SUMX inside my original DAX after RETURN.

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.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors