Forum Discussion
Continued ContributorCount customers making a loss
Hi all
Really hoping someone can help with this measure I am trying to create!
So my data is as follows:
| Customer | |
| ID | Name |
| A | Name A |
| B | Name B |
| C | Name C |
| Forecasts | ||
| CustID | Type | Amount |
| A | Materials | 100 |
| A | Labour | 250 |
| B | Materials | 1250 |
| B | Labour | 3000 |
| C | Materials | 10500 |
| C | Labour | 866 |
| Actuals | ||
| CustID | Type | Amount |
| A | Materials | 22 |
| A | Materials | 335 |
| A | Labour | 54 |
| A | Labour | 111 |
| B | Materials | 25 |
| B | Materials | 2100 |
| C | Labour | 979 |
I need to calculate the count of customers where Actuals.Amount>Forecasts.Amount. So this measure would return 1 using above data, because this is the result if shown in a table:
| Customer | |||
| ID | Name | Forecasts | Actuals |
| A | Name A | 350 | 522 |
| B | Name B | 4250 | 2125 |
| C | Name C | 11366 | 979 |
Only customer A has Actuals.Amount>Forecasts.Amount. So the measure would therefore return 1.
Any idea on how I can write this, as I am completely lost!
Many thanks for all your help
Hi!
I hope this helps answer your question:
Measure =
COUNTX(
FILTER(
SUMMARIZE(
'Customer',
'Customer'[ID],
'Customer'[Name],
"Actual", SUM('Actuals'[Amount]),
"Forecast" , SUM('Forecasts'[Amount])
),
[Actual] > [Forecast]),
'Customer'[ID]
)Regards
Try this:
First create Sum measures for both Sales and Forecast:
Sum of sales = SUM(Sales[sales]) "Actuals" in your example
Sum of Forecast = SUM(Forecast[forecast]) "Forecasts" in your exampleand then:
Obvisously use your customer table instead of my "Sales Product" table. (Assuming your Customer table is linked in one-to-many with both your sales table and forecast table)
11 Replies
JasonTXResolver I
Hi!
I hope this helps answer your question:
Measure =
COUNTX(
FILTER(
SUMMARIZE(
'Customer',
'Customer'[ID],
'Customer'[Name],
"Actual", SUM('Actuals'[Amount]),
"Forecast" , SUM('Forecasts'[Amount])
),
[Actual] > [Forecast]),
'Customer'[ID]
)Regards
- JasonTX
Not sure if you are familiar with SQL, but it is similair to this query (Asuming everything is in one table):
SELECT COUNT(ID) FROM
(SELECT ID , NAME, Sum(ActualAmount), Sum(ForecastAmount)
FROM TABLE
GROUP BY ID, NAME) a
- kentyler
Solution Sage
Interesting problem.
I was not able to solve it in one measure.
I created a measure to calculate whether an individual customer exceeded.
Customer Exceeds Forecast =var TotalActuals = sum(actuals[amount])var TotalForecasts = sum(Forecasts[Amount])return TotalActuals >= TotalForecastsAnd then I used that measure in a calculated column in the customer table.Exceeds = if( [Customer Exceeds Forecast],1,0)I was then able to count the number of customers that exceed with a measure
Count of Customers Where Actuals Exceed Forecast = calculate(sum(Customers[Exceeds]),all(customers))I had to do this in 2 steps because I could not include the true/false condition of actuals exceeding forecast as a filter in a calculate statement , and I needed to use a calculate statement in order to remove any possible filters placed by a report on the customers table (by using ALL()) 
PaulDBrownCommunity Champion
Try this:
First create Sum measures for both Sales and Forecast:
Sum of sales = SUM(Sales[sales]) "Actuals" in your example
Sum of Forecast = SUM(Forecast[forecast]) "Forecasts" in your exampleand then:
Obvisously use your customer table instead of my "Sales Product" table. (Assuming your Customer table is linked in one-to-many with both your sales table and forecast table)
ansa_nazThanks PaulDBrown that works perfecty too, and is even simpler than JasonTX solution. Brill! Cant tell about performance, but I will go with this as I can understand it easier. Cheers everyone!
jthomsonIf you've got the relationships between tables right, it should just be a case of counting the number of rows of your customer table where the sum of the actual column is greater than the sum of the forecast column?
- kentyler
Those totals are calculated inside the measure
var TotalActuals = sum(actuals[amount])var TotalForecasts = sum(Forecasts[Amount])IIf you'd like to do a zoom screen share on this I'm around tomorrow and we can go over it.
