How to dynamically calculate Gap & Over & YTD in Target column Power BI

[rane19]

Goal My Goal is creating a measure or customise column to show how much Gap & Over achieve compare with target then apply on stacked column chart.

The problems is data have more than 1 dimension , if we did that on raw data before import , the report cannot show the correct calculation when we choose overall while ignoring sub-level.

data
TYPE_Layer_1    TYPE_Layer_2    TGT YTD YTD in TGT  GAP OVER
A               orange          100 100 100          0  0
A               apple            50 30  30          20  0
B               orange          120 135 120          0  15
B               apple            60 55  55           5  0
C               orange          130 140 130          0  10
C               apple            70 70  70           0  0

Origin data shape show only the gap over and YTD in target by Type Layer 1 + Type Layer 2 , but the wrong Gap or Over figures show when we sum only Type layer 1:

TYPE_Layer_1    TGT YTD YTD in TGT  GAP OVER
B               180 190 175           5 15

Problem The total of B's Over achieve should be 10 and no Gap but it still show gap 5 & over as 15

Formula in Excel
C = Target , D = YTD
YTD in TGT
=if(C2-D2>=0,D2,if(C2-D2<0,C2))
GAP
=max(C2-D2,0)
OVER
=max(D2-C2,0)

How to achieve this whatever by Measure of Customise column in Power BI ?

tested that not dynamic seems need more formula :

GAP = MAXX(data,data[TGT]-data[YTD])

[Anonymous · Accepted solution]

@rane19 Please create a measure for gap and over 

GAP = 
VAR _tgt = SUM('Table'[TGT])
VAR _ytd = SUM('Table'[YTD])
RETURN IF(_tgt>_ytd,_tgt-_ytd,0)

similarly create a measure for over.