Fix value in the measure

---

v-frfei-msft wrote:

Hi PeterChen ,

 

If we just remove the last line, the result would be shown as below, which doesn’t make sense coz additional blank number occurred in Q3&Q4:

 

Then we add the IF filter to fix it:

IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

BTW, we can replace it using VAR:

WANT = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group]))
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group]))
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
VAR WANT = IF(MAX(dt[Numbers]) = Blank(), Blank(), [NEW])
Return
WANT

 

---

Awesome.

But want seems not work if I add another VAR:

REALWANT = 
VAR MINYR = CALCULATE(MIN(dt[date].[Year]),ALLEXCEPT(dt,dt[Group])) 
VAR MINQR = CALCULATE(MIN(dt[date].[QuarterNo]),ALLEXCEPT(dt,dt[Group])) 
VAR NEW= CALCULATE(SUM(dt[Numbers]), FILTER(ALL(dt), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR)) 
VAR WANTDenominator = IF(MAX(dt[Numbers]) = Blank(), Blank(), [NEW]) 
VAR WANTNominator = CALCULATE(SUM(dt[Numbers]), ALLEXCEPT(dt,dt[Group],dt[date].[Year],dt[date].[QuarterNo])) 
Return
WANTNominator/WANTDenominator 

This return 1 in all other years and quarters, seems weird.

It should return 1 'only' in the first year quarte, said 2015 Q1 under each group.

Year       Quarter      Groups       Numbers       REALWANT

2015       Qtr 1          A               8                     1

2015       Qtr 2          A               16                   2

2015       Qtr 3          A               20                   2.5

2015       Qtr 4          A               16                   2

2016       Qtr 1          A               40                   5

2016       Qtr 2          A               32                   4

2016       Qtr 3          A               12                  1.5

2016       Qtr 4          A               8                    1