Forum Discussion
Problem defining measure in report
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!
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
2 Replies
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