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
christinaxxx
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.

christinaxxx_0-1695243927665.png

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

 

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

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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