DAX help Re: calculated measure

[Anonymous]

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

[v-haibl-msft]

@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

[Anonymous]

@cosborn1231's method worked for me. Thanks for the help guys!

[cosborn1231]

@Anonymous Anytime!

[CahabaData]

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]

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?

[cosborn1231 · Accepted solution]

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.

[CahabaData]

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...