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

 

CustomerNameStatusSalesAmountPY SalesAmount
Customer 1Growing167,08849,588
Customer 2New34,4700.00
Customer 3New5,4000.00
Customer 4Growing11,191436.62
Customer 5Declining1,16427,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
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")

     

11 Replies

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

    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.

  • 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?

  • Huei's avatar
    Huei
    Frequent Visitor
    4 years ago

    Hi Ibendlin,

     

    It's worked, thankyou so much! Appreciate.

     

     

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

      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's avatar
        Huei
        Frequent Visitor
        4 years ago

        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.