Forum Discussion
Average Per Store on Weekly Data
What is your question? What do you need assistance with?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Table 1:
| StoreNo | Store Name |
| 22 | HHH |
| 23 | TTT |
| 32 | NNN |
| 43 | BBB |
| 44 | CCC |
| 45 | OOO |
Table 2:
| StoreNo | Store Name | Percentage | TxnDt |
| 22 | HHH | 10% | 17-10-23 |
| 22 | HHH | 20% | 18-10-23 |
| 22 | HHH | 30% | 19-10-23 |
| 22 | HHH | 40% | 20-10-23 |
| 22 | HHH | 50% | 21-10-23 |
| 22 | HHH | 50% | 22-10-23 |
| 22 | HHH | 50% | 23-10-23 |
| 23 | TTT | 10% | 17-10-23 |
| 23 | TTT | 20% | 18-10-23 |
| 23 | TTT | 30% | 19-10-23 |
| 23 | TTT | 40% | 20-10-23 |
| 23 | TTT | 60% | 21-10-23 |
| 23 | TTT | 60% | 22-10-23 |
| 23 | TTT | 60% | 23-10-23 |
| 32 | NNN | 10% | 17-10-23 |
| 32 | NNN | 20% | 18-10-23 |
| 32 | NNN | 30% | 19-10-23 |
| 32 | NNN | 40% | 20-10-23 |
| 43 | BBB | 20% | 18-10-23 |
| 43 | BBB | 20% | 19-10-23 |
| 43 | BBB | 20% | 20-10-23 |
Result:
| Store No | Store Nam | 17-10-23 | 18-10-23 | 19-10-23 | 20-10-23 | 21-10-23 | 22-10-23 | 23-10-23 | Overall |
| 22 | HHH | 10% | 20% | 30% | 40% | 50% | 50% | 50% | 36% |
| 23 | TTT | 10% | 20% | 30% | 40% | 60% | 60% | 60% | 40% |
| 32 | NNN | 10% | 20% | 30% | 40% | 25% | |||
| 43 | BBB | 20% | 20% | 20% | 20% | ||||
| 44 | CCC | ||||||||
| 45 | OOO | ||||||||
| Total | 20% |
Note: We have a separate Calendar Table in the Report. (I cannot share the sample file or link here as i don’t have access to upload from company Laptop).
Query: 1) Consider Tabl 1 as Unique(Fact Table) in the report.
2) Consider Table 2 as DIM table as we have data of store which has 7 days of data(%), and few store has 4 days of data(%), and few has 3 days of data(%).
I will give Fact table data in the filter all page.
Expecting Result:
There are 3 results expected from the business.
1) When all the data is available in the filter page, we will pick Store Num 22 and 23 (Average) (36% +40%). Result will be 36+40/2 = 38%
2) When we pick Store num 22 and 32 (Average = 36+25/2 = 30.5%)
3) When we pick Store 23 and 44 (Average = 36+(NULL)/2 = 18%)
4) When we pick overall store % (36+40+25+20+NULL+NULL)/6 = 20.16%
Over all %, this should be 44% for this week but there is DIP of 5% due to null value wrongly considered.
Picked 1 store:
Picked 2 Store: 88+43/2 = 65.5 but its showing as 40% as it has selected all the 7 days of the calculation.
Null Value:
Picked the store which has Value and other one is null value:
This is the DAX i have used for the Adoption which is stacked column chart:
Dax used for Weekly(Card Visual)
Not clear what you need beyond the attached.
Note that Table 1 is the dimension table and Table 2 is the fact table.
Thank you for the report.
In the report,
1) if i select Store - 000 and HHH, the average should be 18%.
2) If i select store - TTT and NNN, the average should be 32.5%.
3) The overall % of the store should be(36%+40%+25%+20%)/6 (Total stores = 6), hence the ans should be 20.16.
Hope this can help.Sorry, I cannot agree with that methodology. I hope someone else can help you further.