Forum Discussion
Problem with Count
Hi Everyone,
I am new to Power BI and I have a count problem. In the table below, I am trying to create an analysis of how the delivery days of the following products have changed from Jan - May. The below table shows the product and delivery days in each month. Product A for example was delivered within 90 days in Jan, Feb and Mar, and delivered within 60 days in April and May. Product B however was only delivered within 60 days during the entire period, thereofre it has only one delivery days, e.t.c.
1) How can I count the products with different delivery days (i.e Products A, C and D)?
2) Can you advise on the best visual to represent the Products and their Delivers Days by Month?
PS: The 1s under the months are only representative of the delivery days and not a count of product.
Thank you for your anticipated assistance
| Product | Delivery Days | Jan | Feb | Mar | April | May |
| A | 90 | 1 | 1 | 1 | ||
| A | 60 | 1 | 1 | |||
| B | 60 | 1 | 1 | 1 | 1 | 1 |
| C | 60 | 1 | ||||
| C | 45 | 1 | 1 | |||
| C | 90 | 1 | 1 | |||
| D | 45 | 1 | 1 | 1 | ||
| D | 60 | 1 | 1 | |||
| E | 60 | 1 | 1 | 1 | 1 | 1 |
10 Replies
- mahoneypat
Microsoft Employee
Is this how your data are structured (with Months as columns)? If so, the data should be unpivoted in the query editor. Once that is done, a measure can be written that give the count of products that have >1 values of Delivery Days.
Regards,
Pat
- FrankAT
Community Champion
Hi Anonymous
- Unpivot month columns in Power Query (see figure).
- Use a table visual and the following measure:
Count Delivery Days = SUMX('Table','Table'[Delivery Days] * 'Table'[Value])With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Hi FrankAT,
Thanks for the message. However, I am not trying to count the delivery days. I want to count the product that have more than one delivery days value. For instance, Product A has 2 values in delivery days (i.e. 60 and 90), while Product B has only 1 value in delivery days (i.e. 60).
Update: We can ignore the months columns as I can see that it is causing some comfusion.
