Row value and Grandtotal correct, 2nd level subtotal incorrect (pbix example included)

[omillzy]

I'm working with sales data, we have a fact table containing all the sales pipeline information for live deals in the system. 

The issue i'm having is that my subtotals are not summing correctly, but my grandtotal and single row values are showing as expected. I followed the advice from this quick measures gallery page that greg deckler wrote but it seems my example is slightly different (as I have 3 levels of heirarchy: row, subtotal, grandtotal)

I'm calculating weighted averages by multiplying the lease term certain column, by the deal size, as a proportion out of all deals. Here are the measures (already in the pbix attached) and what they are calculating.

Lease TC (y) = SUM('Table'[Lease TC (y) (single row)])
This measure just sums up the lease term certain value (in years). This is how long we expect a customer to stay with us based on their contract


total fill sqft = SUM('Table'[Total Fill (Sqft)])
This measure is the sum of the total deal size on the customers contract, measured in square foot


weighted average total fill = 
DIVIDE ( [total fill sqft], 
    CALCULATE ( [total fill sqft], 
        ALLSELECTED () 
    ) 
)
This measure calculates the proportion that a single deal is out of all deals showing on the page. This helps us assess the relative importance of a deal, and gives us higher weightings on larger deals etc...


Weighted Avg. Lease TC (y) (incorrect) = 
VAR __table =
    SUMMARIZE(
        'Table',
        'Table'[Opp Name Id],
        "__value", [weighted average total fill]*[Lease TC (y)]
    )
RETURN
    IF(
        HASONEVALUE( 'Table'[Opp Name Id] ),
        [weighted average total fill]*[Lease TC (y)],
        sumx(__table, [__value])
    )
This is my attempt at recreating the measure that Greg Deckler spoke about. As you can see the subtotals are incorrect (compared against the 'Correct' column). At bottom (opp name id) level, the measure is supposed to multiply waeighted average total fill by Lease TC. At the subtotal (opp owner id) level, the measure should perform a sumx on the individual rows. The same should happen at the grand total level

 
As you can see I have a 'Correct' column (i've forced this one in excel) and an 'Incorrect' column (my DAX attempt). They are virtually the same except I need the subtotals to sum correctly. E.g, Opp Owner Id we have 2 deals, 0.13 and 0.33, the subtotal for this person should be 0.46, not 3.54

https://drive.google.com/file/d/1usEX-pRkfWsjfg41nLUsYi-J0KLBfPtR/view?usp=sharing

I hope this is obvious but the 'Weighted Avg. Lease TC (y) (Correct)' column does not exist anywhere in the datamodel, so you will not be able to use this.

Hope i've explained this well enough - but please let me know if you need any more information. Cheers!

[v-stephen-msft]

Hi @omillzy ,

Use the following measure

Measure = IF(ISINSCOPE('Table'[Opp Name Id]),[weighted average total fill]*[Lease TC (y)],SUMX('Table',[weighted average total fill]*[Lease TC (y)]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[omillzy]

Hi @v-stephen-msft unfortunately this does not work - the subtotal values should match the Weighted Avg. Lease TC (y) (Correct) column. I created this column manually in excel as an example to show the community what I need the solution to do.

[v-stephen-msft · Accepted solution]

Hi @omillzy ,

I'm sorry I didn't read the list clearly. Please try this modified measure

Measure = IF(ISINSCOPE('Table'[Opp Name Id]),[weighted average total fill]*[Lease TC (y)],SUMX(ALLEXCEPT('Table','Table'[Opp Owner Id]),[weighted average total fill]*[Lease TC (y)]))

I used the ALLEXCEPT function to calculate the sum grouped by the Opp Owner ID column. Some values are different because of the result of rounding decimals.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[omillzy]

Surely someone can solve this? I feel like it's an easy one (in theory), i'm just not that great at DAX