Count Measure Values

[Huei]

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
Customer 3	New	5,400	0.00
Customer 4	Growing	11,191	436.62
Customer 5	Declining	1,164	27,775

1 ) Calculated Sales Amount using measure 

Sales Amt = CALCULATE(SUM(Sellout[Total Selling]),TREATAS(VALUES(Budget[Brand]),Sellout[BRAND]))+0

2) Calculated PY Sales Amount using measure 

PY Sales Amt = CALCULATE([Sales Amt],SAMEPERIODLASTYEAR(Dates[Date].[Date]))

3) Status (if else) using measure

Status =

IF(

[Sales Amt]<> 0 && [PY Sales Amt] <> 0 && [Sales Amt]>[PY Sales Amt],

"Growing",

IF(

[Sales Amt]<>0 && [PY Sales Amt]<>0 && [Sales Amt]<[PY Sales Amt],

"Declining",

IF(

([Sales Amt])<>0 && [PY Sales Amt]=0 ,

"New",

IF(

[Sales Amt]=0 && [PY Sales Amt]<>0,

"Lost","-"

))))

Question

-----------

How can i calculate the 'Status'? i want to count How many status in Growing, Declining & New

Anyone can please advice?

Thanks

[Huei]

Hi Ibendlin,

It's worked, thankyou so much! Appreciate.

[lbendlin]

Glad to hear.   Just to reiterate - you cannot count measure values. You need to formulate the business requirement and then write another measure that implements the associated logic.

Also note that my first measure is not fully covering all scenarios. (for example when both current and previous year values are blank). Adjust as needed.

[Huei]

Okay, noted. I will check first measure. But i still having problem here, i change to use calculated column for TY sales amount and PY sales amount, but the PY Sales amount show blank. Can you please advise?

Thanks.

[lbendlin · Accepted solution]

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

Because yesterday the first measure you advise was referring to table column. That's why i changed to used calculated column. 

var t= calculate(sum('Table'[SalesAmount]))
var p= CALCULATE(sum('Table'[PY SalesAmount]))

But the latest calculated column version solved my problem.

Many Thanks!! 

[Huei]

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]

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]

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]

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

[lbendlin]

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.

[lbendlin]

Just to confirm - you want this all as measures?  (it would be easier as calculated columns).

You are also missing the scenario when the two values match.