cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors