Forum Discussion
One filter too many?
Hi, I got help in the Community with the following:
CostAllPerCustomer = CALCULATE(SUM('Cost'[Amount]),'Cost'[Type] IN {"All"},'Cost'[Distribution] IN {"Customer"})/CALCULATE(DISTINCTCOUNT('Customer'[CustomerId]),ALLEXCEPT('Customer','Customer'[period]))
This is dividing cost per customer per period. However, some of the costs shall not be distributed on all customers, i.e. cost like
CostAllPerCustomer = CALCULATE(SUM('Cost'[Amount]),'Cost'[Type] IN {"DataCentre"},'Cost'[Distribution] IN {"Customer"})/CALCULATE(DISTINCTCOUNT('Customer'[CustomerId]),ALLEXCEPT('Customer','Customer'[period]))
This one should have a filter like ,'Customer'[CustomerId] IN {"123456"} but I seems to get errormessages trying this, for instance:
CostAllPerCustomer = CALCULATE(SUM('Cost'[Amount]),'Cost'[Type] IN {"DataCentre"},'Cost'[Distribution] IN {"Customer"})/CALCULATE(DISTINCTCOUNT('Customer'[CustomerId]),'Customer'[CustomerId] IN {"123456"},ALLEXCEPT('Customer','Customer'[period]))
Any tips on this one?
JG
If your CustomerID column is numeric data type, remove the quotes around 123456.
Regards,
Pat
3 Replies
If your CustomerID column is numeric data type, remove the quotes around 123456.
Regards,
Pat
JGG , what is the error, The one in the subject ?
Can you a sample pbix after removing sensitive data.
Noe error on the measure but on the visual: Can't load the data.. Calculation error, can't compare Text and Integer.
It doesn't make sense to me since I'm trying to filter, not compare.
