Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filter calculation with a measure...

Hi all, perhaps you could help me?

 

i am trying to find out how manu customers fall into certain revenue categories,

 

for example if a customer spends under £100 in year 2018 i want to perform a disctinc count of the account numbers that do so. Simialerly for £101-£200 and so on for a predetermined number of spend bands.

 

The data is built from sales data that lists the invoice number, customer name and the value of the sale.

 

So my revenue measure would be

 

Calculate(Sum(Table1, Revenue)

 

i tried,

 

Calculate(Disctinctcount(Table1[Customer Number]), [Revenue]<101)

 

but get the error; A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Data is in the format

 

Date, Invoice Number, Customer Number, Product, Rev,

 

 

 

 

Need Help
Tips and Tricks

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago
    Hi

    Try

    Countrows(filter(values(Table1[Customer Number]), [Revenue]<101))
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Zubair_Muhammad that does seem to work as a total, thanks for your help, however,  if i am using this in a table and the table shows a date hierarchy of for example:

       

      Year  Quarter

      2018 Q1

      2018 Q2

      etc

       

      is this calculating how many accounts spent less than £100 in each one of those quarters rather than the year as a whole?

       

      Regards

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

         

        It seems to work on many date hierarchies. Can you share sample data? Please mask the sensitive parts.

         

        Best Regards,
        Dale