Forum Discussion

ansa_naz's avatar
ansa_naz
Icon for Continued Contributor rankContinued Contributor
6 years ago
Solved

Count 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     Cu...
Need Help
  • JasonTX's avatar
    JasonTX
    6 years ago

    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

  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    ansa_naz 

     

    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 example

    and 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_naz's avatar
ansa_naz
Icon for Continued Contributor rankContinued Contributor
6 years ago

Thanks JasonTX  This works perfectly! Now just trying to work out how it works, so I can reuse something similar next time! Cheers!

JasonTX's avatar
JasonTX
Icon for Resolver I rankResolver I
6 years ago

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

  • ansa_naz's avatar
    ansa_naz
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

    that makes perfect sense! so the SUMMARIZE is a subquery combined with a GROUPBY. Will have to use this more often now, cheers