Problem defining measure in report

[Anonymous]

The table below indicates a minimal example of my raw data:

Product	Order Day	Customer	Units Ordered	Units Delivered
P	Apr 1	X	4	3
P	Apr 2	X	4	3
P	Apr 1	Y	3	1
P	Apr 1	Z	3	1
Q	Apr 1	Z	3	1
Q	Apr 2	W	3	2
R	Apr 3	X	1	0
R	Apr 4	Y	2	0
R	Apr 5	Z	8	8
R	Apr 6	Z	6	6

Based on this I am able to create the following table as a PBI report where I give a product summary:

Product	Diff. Customers Ordered	Total Ordered	Total Delivered	Service Rate	Product-level Service Test
P	3	14	8	0.57	1
Q	2	6	3	0.5	0
R	3	17	14	0.82	1

This final column in the above summary checks whether more then 50% is being delivered. This report can be filtered on Order Day (time filter) as well as Customers.

Now, in a similar fashion, I would like to create a customer summary report:

Customer	# diff. Product Ordered	# Products Passing Service Test
X	2	1
Y	2	0
Z	3	1
W	1	1

It basically summarizes the product report after filtering for specific customers. My problem is the final column called "Products Passing Service Test". I am not able to define an appropriate measure in order to get the right numbers displayed in this column. I tried some other approaches but then it does not work well with the time filter on Customer Orders.

Anyone that can help? Thank you very much!

[OwenAuger · Accepted solution]

Hi @Anonymous 

If you have already written a measure Product-level Service Test, then you should be able to write Products Passing Service Test like this (I assumed the table is named Orders) :

Products Passing Service Test = 
SUMX ( 
    VALUES ( Orders[Product] ),
    [Product-level Service Test]
)

This measure iterates over Products visible in the current filter context, and sums [Product-level Service Test] for each. This will respond to any filters you have applied.

Does this work as expected in your model?

Regards,

Owen

[Anonymous]

Thank you very much! This worked as requested. -- AYK