Forum Discussion
Average Per Store on Weekly Data
Hello,
My datasource is enormous where i use CSV file as main file. I have nearly 300+ store list where i have the weekly and daily average percentage value. I need a help on caculating the average for weekly basis.
I'm showing the data for just 7 days(Sunday to Saturday), few stores has null value, few stores has 7 days % and few has 4 days% or 3 days% or 2 days% values. I need to show the overall % for all these stores.
If the Store A has the 7 days value -100+100+100+100+100+100+100/7 = 100%
If the store B has 5 days value - 100+100+30+100+40/5 = 80%
If the Store C has 3 days value - 100+20+70/63.33%
If the Store C has 0 Value - 0%
Weekly = (100+80+63.33+0)Percentage/4 = 60.83%
Weekly % = Var Check =
COUNTROWS('00_SelectDateRange’])
Var Check1 = '04_StoreConsoDaily'[Adoption %]
VAR Result =
DIVIDE(Check1, Check, 0)
RETURN
Result
Note: When i tried to use the dax i received the result without the inclusion of Null%.
This is the formula used for daily % Calcuation
8 Replies
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/1447523Table 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)Not clear what you need beyond the attached.
Note that Table 1 is the dimension table and Table 2 is the fact table.
Hi Anonymous
Try the folloing Dax
Weekly Average % = VAR TotalStores = COUNTROWS(UNIQUE('04_StoreConsoDaily'[StoreNo])) VAR TotalPercentageSum = SUMX( '04_StoreConsoDaily', VAR StoreDays = COUNTROWS('04_StoreConsoDaily') VAR StorePercentage = SUM('04_StoreConsoDaily'[Adoption %]) RETURN DIVIDE(StorePercentage, StoreDays, BLANK()) ) RETURN DIVIDE(TotalPercentageSum, TotalStores, 0)If the above Dax doesn't solve your problem, please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
