How to Sum when using aggregate data table

[jcavl]

I have a datatable that looks like this

order	item	shipped week	shipped month
order 1	item 11	7-Sep	sep
order 1	item 12	14-Sep	sep
order 1	item 13	21-Sep	sep
order 2	item 21	5-Oct	oct
order 2	item 22	5-Oct	oct
order 3	item 31	21-Sep	sep
order 3	item 32	5-Oct	oct
order 3	item 33	5-Oct	oct
order 4	item 44	19-Oct	oct

The total distinct orders are 4, however I need to agggreagte this at a week and month level before bringing the data table into Power BI.
The issue is an order can be shipped across multiple weeks, and sometimes months. Bringing the aggregate data table and using Sum in Power BI fetches me the wrong results.

shipped month	count distinct orders
sep	2
oct	3
sum in pbi	5

shipped week	count distinct orders
7-Sep	1
14-Sep	1
21-Sep	2
5-Oct	2
19-Oct	1
sum in pbi	7

Using the data table as is, and aggregating in Power BI is not feasiable.
How would I show 2 orders in Sept, and 3 orders in Oct with a total of 4 orders shipped, instead of 5 orders shipped in Power BI?  

[Ritaf1983]

Hi @jcavl 

It seems your main issue comes from trying to aggregate without a proper Date table. When an order can appear in multiple weeks or months, simple sums will always overcount.

To fix this:

Ensure the [shipped] column is a real Date (convert it in Power Query if needed).

Create a Calendar table with columns for Date, Month, Year, and WeekOfMonth:

Calendar =
ADDCOLUMNS (
CALENDAR ( MIN ( Orders[shipped] ), MAX ( Orders[shipped] ) ),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMM yyyy" ),
"WeekOfMonth",
WEEKNUM ( [Date], 2 ) - WEEKNUM ( EOMONTH ( [Date], -1 ), 2 ) + 1
)

Relate the Calendar table to your Orders table on [shipped].

Create a measure:

Distinct Orders = DISTINCTCOUNT ( Orders[order_id] )

Use Month or WeekOfMonth from the Calendar table in your visuals.
This will correctly show 2 orders in September and 3 in October, total 4 overall.

If your “week” column is a custom label instead of a real date, follow the same logic — create a helper table with unique week and month values and relate it (or use it as a slicer).

For more details:

https://radacad.com/all-in-one-script-to-create-calendar-table-or-date-dimension-using-dax-in-power-...

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[jcavl]

Hi Rita<
Thanks for the detailed answer, but this is an issue of double counting distinct orders. The original data table has over 50 cols, and 70 mil rows and counting. I cannot bring that data table into Power BI. I need to aggregate the table in SQL before attempting to bring it into Power BI.

[v-menakakota]

Hi @jcavl ,
Thanks for reaching out to the Microsoft fabric community forum.  

Thanks for the update. The suggestion to aggregate in SQL using distinct combinations (for example, order + shipped_month) is correct and should help avoid double counting before the data reaches Power BI.

However, since the issue involves large data volumes and complex aggregation logic, it may need deeper review of your SQL model and data structure.

If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.

we truly appreciate your active participation in the Microsoft Fabric Community.

Best Regards, 
Community Support Team

[v-menakakota]

Hi @jcavl ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the Power BI Parteners? 

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. 

Thank you.

[jcavl]

Ticket not opened. Issue (un)resolved. Report no longer needed.

Thank you. 

[jcavl]

Question can be closed.
I don't know how to do that.

[v-menakakota]

Hi @jcavl ,

Thank you for the update.

[Gabry]

Hey man,

I think you just need to build a measure like this:

Order count= DISTINCTCOUNT('Table'[order])

[jcavl]

Hi, 
This is an issue of double counting distinct orders. The original data table has over 70 mil rows. I cannot bring that data table into Power BI and then create a measure. I need to aggregate the table in SQL before attempting to bring it into Power BI.