cancel
Showing results for
Did you mean:
Helper I

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

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])`

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

Anonymous
Not applicable

@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.

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!