Monthly total by branch

[cocomy]

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

[MFelix]

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

[cocomy]

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

[MFelix · Accepted solution]

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:

Regards,

MFelix

[cocomy]

MFelix,

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

All the best,

cocomy

[Ellie1979]

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?

[Greg_Deckler]

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

[cocomy]

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

[Greg_Deckler]

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