cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Summing a count measure in a table

Hello,

I'm trying to get a sum of total deliveries made to a customer.

If a customer received a delivery, I've created to a measure to indicate a 1 and if a customer did not receive a delivery on a day, that will be blank.

I would like to be able to sum all the '1' across each day to get a total number of deliveries.

Addtionally, I wanted to caluclate how many delveries per week does the customer receive (a week would be Sunday through Saturday).  

 

Below is an example of how the data looks in a table in Power BI:

 

Capture.PNG

 

Thank you for your help.

 

 

 

 

 
1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @ahmoosvi ,

 

If there are multi receive each day but still indicate a 1, We can use the following steps to meet your requirement.

 

1. Create a measure to count the delivery.

 

Measure = IF(CALCULATE(COUNT('Table'[delivery]),FILTER('Table','Table'[delivery]="Y"))>0, 1,blank())

 

summing 3.jpg

 

2. Then create a date table, and create an one-to-many relationship between two tables.

 

date = ADDCOLUMNS(ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],1)),"year_week",[year]&"-"&"WK "&[week])

 

Summing1.jpg

 

Summing2.jpg

 

3. Then we can create a measure and a new matrix table using [year-week] column.

 

CountMeasure = 
SUMX (
    DISTINCT ( 'date'[Date] ),
    SUMX ( GROUPBY ( 'Table', 'Table'[CustomerID] ), CALCULATE ( [Measure] ) )
)

 

summing 4.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

If it does not meet your scenario, Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

 

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here.

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @ahmoosvi ,

 

If there are multi receive each day but still indicate a 1, We can use the following steps to meet your requirement.

 

1. Create a measure to count the delivery.

 

Measure = IF(CALCULATE(COUNT('Table'[delivery]),FILTER('Table','Table'[delivery]="Y"))>0, 1,blank())

 

summing 3.jpg

 

2. Then create a date table, and create an one-to-many relationship between two tables.

 

date = ADDCOLUMNS(ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"week",WEEKNUM([Date],1)),"year_week",[year]&"-"&"WK "&[week])

 

Summing1.jpg

 

Summing2.jpg

 

3. Then we can create a measure and a new matrix table using [year-week] column.

 

CountMeasure = 
SUMX (
    DISTINCT ( 'date'[Date] ),
    SUMX ( GROUPBY ( 'Table', 'Table'[CustomerID] ), CALCULATE ( [Measure] ) )
)

 

summing 4.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

If it does not meet your scenario, Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

 

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here.

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

Super User III
Super User III

@ahmoosvi ,

 

You can check this solution out.

 

https://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-d...

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Thank you for your help.  Got it to work!

Helper II
Helper II

Hi @ahmoosvi 

In calendar create week column using WEEKNUM function.
plot week  and date in a column in visual matrix & plot your value in value field as a number 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors