Have a specific field value count as one of each of two other values

[jnpb]

I have a measure that counts rows to get a total number of orders YTD. It's built like this:

Orders YTD = CALCULATE(COUNT('PurchaseReport'[OrderId]),DATESYTD('PurchaseReport'[Created].[Date]))

A slicer in my my report then filters by a specific product category. Data is structured in this way:

OrderID      Product Category

23458         Apple

08821         Orange

12340         Banana

81237         Apple/Orange

58342         Apple/Banana

The idea being that slicing by product category gives the YTD order total per category.

However, we have a combination product type for which a count of 1 should be applied to both products (e.g. Apple/Orange should count as 1 count in Apple, 1 count in Orange). Since the Orders YTD simply counts the column for the total number of orders, I don't know how to work this in so that anytime a combination product type value is given, it treats each as its' own row count for that product.

Assuming I have to rethink how I'm tabulating orders per product type, what would be the right approach here?

Thanks!

[Ashish_Mathur]

Hi,

Once you have split data into rows (as suggested by FreemanZ), follow these steps

1. Create a Calendar Table

2. Create a relationship (Many to One and Single) from the Created column to the Date column of the Calendar Table

3. To your visual, drag the Date from the Created column

4. Write these measures:

Total = COUNT('PurchaseReport'[OrderId])

Total YTD = calculate([Total],datesytd(Calendar[Date],"31/12"))

Hope this helps.

[FreemanZ · Accepted solution]

hi @jnpb 

You would need to split the column to rows firstly in Power Query, like this:

https://community.powerbi.com/t5/Desktop/Power-Query-Split-data-onto-individual-rows/td-p/1928351