cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rane19
Helper I
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. 

View solution in original post

1 REPLY 1
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. 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!