cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cocomy Member
Member

Monthly total by branch

Hi All,

 

I am trying to calculate monthly sales by branch from actual sales table. (if no sales, no data)

 

Date                          Branch           Sales        Items

1 Jan 2018                 NY                 50            Lipstick

15 Jan 2018               NY                 40            Mascara

15 Jan 2018               LA                  20            Cream

20 Jan 2018               Seattle           15            Lipstick

20 Jan 2018               Seattle           30            Cream

27 Jan 2018               LA                  25            Cream

31 Jan 2018               NY                 50            Mascara

31 Jan 2018               NY                 45            Mascara

1 Feb 2018                 LA                 20            Cream

continue'd

 

I would like to have monthly sales by branch.  I created branch table and map relationship ... etc

For some reason, I get same number (185+65+45) for all branches in January.

 

Could you please advise how to do it?

 

The following table is what I am aiming for.  

Date                          Branch              Sales

1 Jan 2018                NY                     185

1 Jan 2018                LA                      65

1 Jan 2018                Seattle                45

1 Feb 2018               NY

1 Feb 2018               LA

1 Feb 2018               Seattle

continue'd

 

Really appreciate your suggestion.

 

All the best,

cocomy

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Monthly total by branch

Hi @cocomy,

 

Are you sure about your relationships?

 

I made a mockup model with your setup and go the correct result make for each month:

 

table.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
Super User
Super User

Re: Monthly total by branch

Make sure that your relationship direction is correct between your two tables.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

cocomy Member
Member

Re: Monthly total by branch

Hi Greg,

 

As per your suggestion, I just played around relationship but did not work.

 

I guess it is party because actual sales is not even daily and I try to summarize to month by branch.

 

All the best,

cocomy

 

Super User
Super User

Re: Monthly total by branch

So, do the tables presented represent the actual tables. I get a sense that they do not so I want to clarify.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Monthly total by branch

Hi @cocomy,

 

One question in your table you have several dates in the month but in the final outcome you only have first day of every month.

 

Do you have a calendar table also and have a relationship with the sales table?

 

Are you using the fields from the correct table to fill up your visual if you have relationship active and use diffent fields the outcome can be return all the values or none of the values.

 

Regards,

MFelix

 

 



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

Proud to be a Datanaut!




cocomy Member
Member

Re: Monthly total by branch

Hi M Felix,

 

Yes, I created monthly calendar table and also added one column to actual sales to show monthly date to create relationship. (1 Jan 2018, 1 Feb 2018....)

 

Relationship is shown in different colour below.

 

(1) Actual

Date                          Branch           Sales        Items            Monthly Date

1 Jan 2018                 NY                 50            Lipstick         1 Jan 2018

15 Jan 2018               NY                 40            Mascara        1 Jan 2018

15 Jan 2018               LA                  20            Cream          1 Jan 2018

20 Jan 2018               Seattle           15            Lipstick         1 Jan 2018

20 Jan 2018               Seattle           30            Cream          1 Jan 2018

27 Jan 2018               LA                  25            Cream          1 Jan 2018

31 Jan 2018               NY                 50            Mascara       1 Jan 2018

31 Jan 2018               NY                 45            Mascara       1 Jan 2018

1 Feb 2018                 LA                 20            Cream          1 Feb 2018

continue'd

 

(2) Calender

Monthly date

1 Jan 2018

1 Feb 2018

1 Mar 2018

 

(3) Branch

Branch

NY

LA

Seattle

 

(4) Monthly Sales I am aiming for on vizualization is

(2)Monthly date   (3)Branch          (1)Sales

1 Jan 2018            NY                    185

1 Jan 2018            LA                     65

1 Jan 2018            Seattle              45

1 Feb 2018           NY

1 Feb 2018           LA

1 Feb 2018          Seattle

 

I like to try DAX fomula below but still struggling how to express month.  Could you please kindly help?

 

Monthly total =

VAR select_date =
    MIN (Calender[Monthly_date])
VAR end_Date = ????
VAR Branch_select =
    MAX (Branch[Branch])
RETURN
    CALCULATE (
        SUMX (Actual,[Sales]),
        FILTER (
            ALL (Actual),
           Actual[Monthly_date] <= select_date
                && ??? <=end_Date
                && Actual[Branch] = Branch_select
        )
    )

 

All the best,

cocomy

 

 

Ellie1979 Frequent Visitor
Frequent Visitor

Re: Monthly total by branch

Not sure on the formula, does a matrix table not give you the result you need pulling in Calendar.MonthlyDate as row headers, Branch.Branch as the column headers and Actual.Sales as the values then making sure the aggregation is set to sum?

Super User
Super User

Re: Monthly total by branch

Hi @cocomy,

 

Are you sure about your relationships?

 

I made a mockup model with your setup and go the correct result make for each month:

 

table.png

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post

Highlighted
cocomy Member
Member

Re: Monthly total by branch

MFelix,

 

Thank you very much again. Relationship was incorrect so I got the same number for all apparently.!

 

All the best,

cocomy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 271 members 2,991 guests
Please welcome our newest community members: