cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VendettaBob Frequent Visitor
Frequent Visitor

Count Filtered SUM Measure

Hi,

 

I have data in the following format:

 

Date                        Reg                     Distance Miles

20190101                Veh 1                    0

20190101                Veh 1                    0

20190101                Veh 2                     123

20190102                Veh 1                     1

20190102                Veh 1                     0

20190102                 Veh 2                   0

20190102                  Veh 3                  0

20190102                 Veh 3                   0

 

Distance Miles is already a measure. What I need to do is to Count the number of Distinct 'Reg' where the sum of the 'Distance Miles' = 0

 

So the answer above would be:

20190101     1     (Veh 1)

20190102     2     (Veh 2, Veh 3)

 

Many thanks,

2 REPLIES 2
Highlighted
Super User
Super User

Re: Count Filtered SUM Measure

Hi @VendettaBob 

1. Place Table1[Date] in a table visual

2. Place this measure in the visual

Measure =
SUMX ( DISTINCT ( Table1[Reg] ), INT ( [Distance Miles] = 0 ) )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

 

VendettaBob Frequent Visitor
Frequent Visitor

Re: Count Filtered SUM Measure

Hi,

 

Thanks for this. This would work in a graph with the date on the x axis.

 

However, I also need this value in a tile. So would need to incorporate the date element into the calc. How would I do this?

 

Thanks,

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 310 members 3,312 guests
Please welcome our newest community members: