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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors