topic Re: Calculate Subtotal From a Template - Finance in DAX Commands and Tips

Calculate Subtotal From a Template - Finance

H_insight — Thu, 06 Jul 2023 21:42:43 GMT

Hiya,

I am using a custom template (Disconnected table) inside power bi to help me deliver a business layout.

Template:

I have created the following measures:

Base measure:

Total Amount = SUM('FACT'[Value])

Amount measure for Plan & Actual:
Amount (,000) =

VAR _Actual = CALCULATE([Total Amount], 'FACT'[Plan] = "A")
VAR _Plan = CALCULATE([Total Amount], 'FACT'[Plan] = "P")

Return
IF(
SELECTEDVALUE('Custom View'[Type]) = "Actual", _Actual,
IF(SELECTEDVALUE('Custom View'[Type]) = "Plan", _Plan,
IF(SELECTEDVALUE('Custom View'[Type]) = "VAR", (_Plan - _Actual),0)))+0

Dynamic measure to get me values per selected (In month , YTD, Full Year)

Dynamic Measure =
VAR _CurrentItem = SELECTEDVALUE(Template[SK])
VAR _Title = SELECTEDVALUE('Custom View'[Title])
VAR _Type = SELECTEDVALUE('Custom View'[Type])
VAR _InMonth = CALCULATE( [Amount (,000)], FILTER('FACT','FACT'[SK] = _CurrentItem))
VAR _category = SELECTEDVALUE(Template[Category])

VAR _YTD = CALCULATE(
[Amount (,000)],
FILTER(ALL('Calendar'), 'Calendar'[Date] IN DATESYTD('Calendar'[Date])),
'FACT'[SK] = _CurrentItem
)

VAR _FullYear = CALCULATE(
[Amount (,000)],
FILTER(ALL('Calendar'), 'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year])),
'FACT'[SK]= _CurrentItem
)

Return
SWITCH(TRUE(),
_Title = "In Month", _InMonth,
_Title = "YTD", _YTD,
_Title = "Full Year", _FullYear,
BLANK()
)

The below matrix shows the correct values, but what I am struggling to get is the SubTotals per the layout.

i.e. "Finance Subtotal" = (SK1+SK2+SK3+SK4)

Missing Output:

Any help is greatly appreciated.

Attached sample PBIX File .

Many thanks

Re: Calculate Subtotal From a Template - Finance

Greg_Deckler — Thu, 06 Jul 2023 21:54:38 GMT

H_insight First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Calculate Subtotal From a Template - Finance

H_insight — Fri, 07 Jul 2023 06:17:53 GMT

Thanks Greg_Deckler .

It's remarkable to realize that this concept was initially proposed back in July 2018, and its 5 YEARS anniversary is fast approaching without any resolution in sight!

I see your approach of having "No Calculate", but for some reason, I am not able to apply it to my disconnected table. any chance you can provide the DAX code for me to explorer, please?

So far, here is my attempt:

m_Total 4 =

VAR __table = SUMMARIZE('FACT','FACT'[SK], 'FACT'[Date], 'FACT'[Plan],"__value",[Total Amount])

RETURN

IF(HASONEVALUE(Template[SK]),[Total Amount],SUMX(__table,[__value]))
but I am getting the same values everywhere, which is normal as there is no relationship between 'Template' and 'FACT' tables.