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")

     

Huei's avatar
Huei
Frequent Visitor
4 years ago

Hi Ibendlin,

 

I'm New in Power BI. If use calculated columns meaning all measures change to calculated columns for Sales amount, PY Sales amount and status?

 

Thanks

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.