Subtotal of A Measure

[glove80]

I have a very complex set of measures that ends up in a table.  The subtotal of the measure is not adding up because it is taking the average percentage of one measure and multiplying it by the total of another measure instead of adding up the values in each row.  I have tried using the method with SUMMARIZE, HASONEVALUE and SUMX, and it still doesn't work.  It is likely due to the fact that the submeasures are reassigning buckets to other buckets (Work Order Class Map) and performing functions based on bucket and region (Big Region).  The measure I want to subtotal correctly is called "VTB $".  Can anybody analyze the below measures and assist?

VTB $ = [PY Attain %] - [Attain to Bud %]) * [Adj Bud $]

PY Attain % = [Adj PY $ TODAY]/[Adj PY $]

Attain to Bud % = [Adj Act $] / [Adj Bud $]

Adj Bud $ = CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")) + [Adj Bud Amt]

Adj PY $ TODAY =

var MinDate = CALCULATE(MIN(_dtDateDim[Date]),ALLSELECTED('_dtDateDim'))

RETURN

CALCULATE([Adj Act $],SAMEPERIODLASTYEAR(DATESBETWEEN('_dtDateDim'[Date],MinDate,TODAY())))

Adj PY $ = CALCULATE([Adj Act $],SAMEPERIODLASTYEAR('_dtDateDim'[Date]))

Adj Act $ = CALCULATE([Act $],FILTER('Finance Data','Finance Data'[AccountCodeBizOpsMapping] <> "M&R - Other")) + [Adj Amt]

Adj Bud Amt = CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] = "M&R - Other"))*[Budget Breakout]

Budget Breakout =

var NormalBreakout = DIVIDE(CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER('Work Order Breakout (Closed)','Work Order Breakout (Closed)'[WorkOrder_Class] <> "M&R - Other")),CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")))

var HQOtherBreakout = DIVIDE(CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")),CALCULATE(SUM('Budget Data'[Sum of Native Cost Consolidated (USD)]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")))

RETURN

IF(MAX('Finance Region Mapping'[Big Region]) = "HQ/Other",HQOtherBreakout,NormalBreakout)

Adj Amt = CALCULATE(SUM('Finance Data'[InvoiceAmountUSD]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] = "M&R - Other"))*[Breakout %]

Breakout % = DIVIDE(CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER('Work Order Breakout (Closed)','Work Order Breakout (Closed)'[WorkOrder_Class] <> "M&R - Other")),CALCULATE(DISTINCTCOUNT('Work Order Breakout (Closed)'[WorkOrder_Name]),FILTER(ALL('WorkOrderClassMap'[Work Order Class Map]),'WorkOrderClassMap'[Work Order Class Map] <> "M&R - Other")))

Act $ = SUM('Finance Data'[InvoiceAmountUSD])

[Savio1929]

Hi i too have a similar problem and new to Power Bi. I would appreciate is someone could assist me

I have a basic dax measure  Total Cost =Qty x Unit Cost

When is sumarize ,the Total Cost column is not summarizing

Below is a part no called Ily Beans and the subtotal shows 13.536 which is incorrect

Appreciate a work around for this

Thanks

Savio

[v-easonf-msft]

Hi , @glove80 

Sample data  and expected result  will make it easier for us to understand and  solve your problem.

Or ,you can take a screenshot of your table visual  so that we can understand the fields and measures you put in.

Best Regards,
Community Support Team _ Eason

[glove80 · Accepted solution]

I found the solution myself.  I used GROUPBY instead of SUMMARIZE to retain some of the filters that I had passed along in the measures that make up [VTB $].  I did this:

Adj VTB $ = IF(HASONEVALUE('WorkOrderClassMap'[Work Order Class Map]),[VTB $],SUMX(GROUPBY('WorkOrderClassMap','WorkOrderClassMap'[Work Order Class Map]),[VTB $]))

and then passed it into this:

Adj VTB $ Fixed =

var SummarizedTableRegion = SUMMARIZE('Finance Region Mapping','Finance Region Mapping'[Big Region],"RegionBreakout",[Adj VTB $])

RETURN

IF(HASONEVALUE('Finance Region Mapping'[Big Region]),[Adj VTB $],SUMX(SummarizedTableRegion,[RegionBreakout]))

[Greg_Deckler]

Try looking at this: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

You should be able to use this technique to have completely different calculations at any level.