Average Per Store on Weekly Data

[Anonymous]

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

Adoption % =

Var A = SUM('04_StoreConsoDaily'[NewAdoption])

Var B = CALCULATE(COUNT('Select Store'[StoreNo]), ALL('00_SelectDateRange'))

RETURN

A/B

[Anonymous]

Thank you for the Reply.

@PowerBICommunity - Can someone help on this query.

[Anonymous]

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.

[Anonymous]

This DAX doesnt work as it says Unique is not a function. 

[lbendlin]

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-...

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/1447...

[Anonymous]

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]))

RETURN

A/B

Dax used for Weekly(Card Visual)

Weekly % = Var

A=

Calculate(count('00_SelectDateRange'[Day]),Filter('04_StoreConsoDaily','04_StoreConsoDaily'[Adoption %]>0))

Var

B =

[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.

[Anonymous]

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.

[lbendlin]

Sorry, I cannot agree with that methodology.  I hope someone else can help you further.