Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
3 years ago

Calculate Subtotal From a Template - Finance

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

2 Replies