Forum Discussion
Average Per Store on Weekly Data
Super UserWhat 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:Adoption % =Var A = SUM('04_StoreConsoDaily'[NewAdoption])Var B = CALCULATE(COUNT('Select Store'[StoreNo]), ALL('00_SelectDateRange'[Day]))RETURNA/B
Dax used for Weekly(Card Visual)Weekly % = VarA=Calculate(count('00_SelectDateRange'[Day]),Filter('04_StoreConsoDaily','04_StoreConsoDaily'[Adoption %]>0))VarB =[Adoption %]Return (B/A)- lbendlin
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.