Forum Discussion
count rows as subtotal
Hi, I'm building a report that would have count rows as subtotal, not the actual sum of the values during the date period. From current view to count distinct row view, how can I manipulate to have this view?
Thank you in advance!
| Current View | ||||
| ID | 17-Jun | 18-Jun | 19-Jun | Total order |
| 123456 | 5 | 1 | 0 | 6 |
| Count row view | ||||
| ID | 17-Jun | 18-Jun | 19-Jun | Ordered days |
| 123456 | 5 | 1 | 0 | 2 |
sunah132 , You need to create a measure that calculates the number of days with orders for each id
OrderedDays =
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
TableName,
TableName[ID],
TableName[DateColumn],
"OrderCount", SUM(TableName[OrderCount])
),
[OrderCount] > 0
)
)
)I have attached PBIX with your sample data
2 Replies
sunah132 , You need to create a measure that calculates the number of days with orders for each id
OrderedDays =
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
TableName,
TableName[ID],
TableName[DateColumn],
"OrderCount", SUM(TableName[OrderCount])
),
[OrderCount] > 0
)
)
)I have attached PBIX with your sample data
Thank you! It helped exactly how I was tried to solve. 🙂
