Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ballist1x
Helper III
Helper III

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,

 

 

 

 

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

Hi

Try

Countrows(filter(values(Table1[Customer Number]), [Revenue]<101))

Regards
Zubair

Please try my custom visuals

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

 

Hi @ballist1x,

 

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

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.