Summing a count measure in a table

[Anonymous]

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:

Thank you for your help.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

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())

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])

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] ) )
)

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.

[harshnathani]

@Anonymous ,

You can check this solution out.

https://community.powerbi.com/t5/Desktop/calculate-n-days-sales-for-locations-with-different-start-dates/m-p/723883

Regards,

Harsh Nathani

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

[Anonymous]

Thank you for your help.  Got it to work!

[nahid3152]

Hi @Anonymous 

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