Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to 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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |