Forum Discussion

PeterChen's avatar
PeterChen
Icon for Helper I rankHelper I
7 years ago
Solved

Fix value in the measure

I want to create a VAR in the measure that calculate the number by groups and year and quarter.

For example,

 

Date              Groups    Numbers

2015/1/1       A             1

2015/1/2       A             2

2015/2/1       A             5

2015/4/1       A             2

2015/4/2       A             10

2015/6/5       A             4

 

What I want in report is:

 

Year       Quarter      Groups       Numbers       NEW

2015       Qtr 1          A               8                     8

2015       Qtr 2          A               16                   8

 

The NEW is the total value of min([Date].[Year]) & min([Date].[Quarter]) by Groups.

 

NEW = 
VAR MINYR = MIN(dt[date].[Year])
VAR MINQR = MIN(dt[date].[QuarterNo])
VAR NEW= CALCULATE(SUM(dt[total]), FILTER(ALLEXCEPT(dt, dt[Groups]), dt[date].[Year] = MINYR && dt[date].[QuarterNo] = MINQR))
Return NEW
However, this return:
 

Year       Quarter      Groups       Numbers       NEW

2015       Qtr 1          A               8                     8

2015       Qtr 2          A               16                   16

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi PeterChen ,

     

    To create a measure as below.

     

    NEW = 
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))
Return 
IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi PeterChen ,

     

    To create a measure as below.

     

    NEW = 
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))
Return 
IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

    • PeterChen's avatar
      PeterChen
      Icon for Helper I rankHelper I
      7 years ago
      v-frfei-msft wrote:

      Hi PeterChen ,

       

      To create a measure as below.

       

      NEW = 
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))
Return 
IF(MAX(dt[Numbers]) = BLANK(),BLANK(),NEW)

      Could you please explain the last line?

      What does Blank() mean?

      Is it possible to assign a VAR to that?

      For example, WANT = IF(MAX(dt[Numbers]) = Blank(), Blank(), New)

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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