Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix,
Thank you very much again. Relationship was incorrect so I got the same number for all apparently.!
All the best,
cocomy
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?
Make sure that your relationship direction is correct between your two tables.
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
So, do the tables presented represent the actual tables. I get a sense that they do not so I want to clarify.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |