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

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

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