cancel
Showing results for
Did you mean:
Helper III

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

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

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!

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Helper III

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.

Community Support

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.

Helper III

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!