Please advise how to compute the sales per square feet by branch and total. Currently, the measure which I have used gave me sales per sq ft based on the total branch floor area, instead of by branch. I have the following datasets:-
Daily Sales table
Cost centre Date Amount
Branch floor area
Branch Cost centre Trading Area (Sq ft)
Measure which I have used are as follow:-
Total sales= sum(sales amount)
Total Branch floor area= sum(branch floor area)
Average sales= Averagex(values('Date'[MonthnYear]), [Total sales])
Sales per sq ft= Divide( [Average sales], [Total Branch floor area)]
Ok reviewing this and trying to get my mind into what you have here.
You want to calculate the sales by branch, which context do you have these formulas in?
I think you might have to show some images of the results in a table here.
If you want to work out a total number by something, then think about iterating through that particular dimension within a iterating function.
Something like AVERAGEX( VALUES( Branches), …
It’s hard to say exactly though without see the initial context and also the relationships in the data.
Potential you need to use RELATED as well. Because I’m just presuming you Branch Floor area is a lookup table and you need to compare it with the results in your fact table.
As I say it’s hard to know without all the information unfortunately.
The image is quite small and blurry so it’s hard to see everything, but from what I can tell…
BR is in your table, but that has no way to filter the Trading Area because that sits currently in the other ‘lookup table’.
So there’s no filter being applied to the result? Or is there? Where is the BR coming from?
It the Total Branch floor are actually calculating correctly for each row in that table?
Are you also saying that it calculates correctly when everything is in the table, but when you remove the one measures it doesn’t? Can can’t be true as it shouldn’t matter in this case.
Sorry this need to be clearer, I’m trying to find a solution but I don’t have enough clear information around what we’re dealing with here.
Hopefully this is clearer. Tables, stores list and branch floor area are connected to sales table by COST CENTRE.
Measure “Sales per sq feet” are calculated correctly for each row in the table. However, when I remove trading area which is from look up table, “Sales per sq feet” for branch is not correct.
Please advise how to correct the measure for sales per sq feet. Thanks.
Ok let’s work through this. I really recommend breaking things down as much as possible here and really thinking as the why the calculation (the square feet calc I mean is not working.
In your table you are filtering by BR right? This is coming from your Sales table?
If that is the case then how is the Trading Area going to filter correctly when its in the lookup table?
Please really think deeply about how you relationships are working here…
For reviewing how the model and relationships work, please check out this course
When you have your results in this table, how do you think this result is being calculated?
This is the key. What filters are being put in place for you to get this result? Once you understand this then it will be easier to solve.
Passed this I’m struggling to get you the right answer without seeing the model and testing it.
But I also want to get you to a position where you can work this out also. It just requires some deeper thinking about how specific results are being calculated and then once you break it down you can understand more about what is required to get the right answer.
