Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

DAX help Re: calculated measure

Hello,

 

Heirarchy of my sales data is as follows, EVP->RVP->DM->Branch->Customer->Part.. I would like to set up a measure that counts if a customer has a profit gain at a certain value (Red).

 

I wrote the following code, which I was hoping would sum up every time the if/then was true. When I summarize at a higher level, it just recalculates for that level and doesn't sum at the Customer level. I'm guessing the issue has to do with the filter, but am not sure how to adjust.

 

if(
SUMX(
filter(

'Vending Guidnace Proposal','Vending Guidnace Proposal'[Customer]='Vending Guidnace Proposal'[Customer]), 'Vending Guidnace Proposal'[Profit Gain @ Red])>0,1,""
)

 

Thanks

Need Help
  • cosborn1231's avatar
    cosborn1231
    10 years ago

    Hi Rynoh17,

     

    I usually will leverage a calculated column in this case and build a associated measure.

     

    Sum of Extended = IF([Sales Amount]>100,1,0)

    Count of Customers = CALCULATE(DISTINCTCOUNT('Power BI'[Customer ID]),FILTER('Power BI','Power BI'[Sum of Extended]=1))

     

    Applying your different filter contexts should show at which branch your customers who meet that extend sales requirement fall.

     

    Within the table/matrix  you can also leverage conditional color formatting within the values tab to enforce color threshholds.

     

    I hope this helps.

7 Replies

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    10 years ago

    I don't fully follow your post - in that you state you seek a Count however the statement example is a SUM.  Also it is not clear as to how your hierarchy is set in tables......

     

    But in any case - I believe that you establish your Measure at the Customer level using the appropriate table.

     

    Then if your hierarchy is joined with join lines between tables appropriately then the embedded drill up/down should work as you add these fields into Visuals.

     

    But alot depends on the data model as to what data is where and it is difficult to provide more specific advice otherwise.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Sorry for lack of clarity..

       

      I have multiple parts per customer with extended sales at a certain price point, some positive and others negative. What I would like to do is sum the total extended sales per customer (sumifs in excel) and count it as 1 if it is a positive result and nothing if it is a negative result.

       

      From here I want to count  the customers with a 1, per level (EVP, RVP, DM, Branch). Say there are three customers to a branch with positive extended sales. In a table or matrix form, I would want that branch value to read 3, (the sum of all three 1s). At the RVP level, I would want the count of all the positive extended values from the customers underneath it.

       

      Does this help?

      • CahabaData's avatar
        CahabaData
        Icon for Memorable Member rankMemorable Member
        10 years ago

        so to chip away at this ; step one is to establish:

        sum the total extended sales per customer

         

        do you have this much done? - if not  a snippet of the table sample would help

         

        once that is established then one would Count Rows with a Filter of only positive values

         

        As per my first post - depending on your hierarchy (sum by Region, VP, etc) the Visuals should auto aggregate up/down...if they are related appropriately either all in the same table or joined from separate tables...

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Anonymous

     

    Please try the solutions provided by CahabaData and cosborn1231. If you still cannot get the expected result, maybe you can give some sample data and the expected output to us.

     

    Best Regards,

    Herbert