Forum Discussion

Huei's avatar
Huei
Frequent Visitor
4 years ago
Solved

Count Measure Values

Hi Guys,   I need help,    Below are my current result   CustomerName Status SalesAmount PY SalesAmount Customer 1 Growing 167,088 49,588 Customer 2 New 34,470 0.00 Custome...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    "i change to use calculated column"

     

    Why?  I thought your goal was to do everything with measures?

    Here is the formula for the calculated column version:

    Column Group = 
SWITCH(TRUE(),
ISBLANK([PY SalesAmount]) &&  ISBLANK([SalesAmount]),"Inactive",
ISBLANK([PY SalesAmount]) &&  not ISBLANK([SalesAmount]),"New",
not ISBLANK([PY SalesAmount]) &&  ISBLANK([SalesAmount]),"Lost",
[SalesAmount]>[PY SalesAmount],"Increasing",
[SalesAmount]<[PY SalesAmount],"Declining",
"Same")

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

Calculated columns are only computed once during dataset refresh.  Measures are recomputed each time the user interacts with the report.  More flexibiliy, at the expense of more compute.

Huei's avatar
Huei
Frequent Visitor
4 years ago

I had tried to use calculated columns for all the measure, but the result was wrong that's why i use all measure. Can i count the measure?

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Not really, you need a separate measure for that. (It is the recommended approach anyway).

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      Here is the first measure:

       

       

      Group = 
var t= calculate(sum('Table'[SalesAmount]))
var p= CALCULATE(sum('Table'[PY SalesAmount]))
return if(HASONEVALUE('Table'[CustomerName]),switch(TRUE(), ISBLANK(t),"Lost",ISBLANK(p),"New",t>p,"Growing",p>t,"Declining","Same"))

       

      Here is the second measure:

      Customers = 
var s = SELECTEDVALUE(Groups[Group])
var a = ADDCOLUMNS(VALUES('Table'[CustomerName]),"TY",calculate(sum('Table'[SalesAmount])),"PY",calculate(sum('Table'[PY SalesAmount])))
var b = ADDCOLUMNS(a,"Match",SWITCH(TRUE(),
s="Lost" && not ISBLANK([PY]) && ISBLANK([TY]),1,
s="New" && not ISBLANK([TY]) && ISBLANK([PY]),1,
s="Declining" && not ISBLANK([TY]) && not ISBLANK([PY]) && [TY]<[PY],1,
s="Growing" && not ISBLANK([TY]) && not ISBLANK([PY]) && [TY]>[PY],1,
s="Same" && not ISBLANK([TY]) && not ISBLANK([PY]) && [TY]=[PY],1,
0))
return if(hasonevalue(Groups[Group]),sumx(b,[Match]))

      See attached.