Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
Here are my tables:
1. Table "Full Data" which has all Store names and related transaction data:
Store Name | Order Quantity | Product Type | Posting Date |
2. Table "Threshold" has a few Store names (A, E, H) that I want to track and the highlighted columns are the calculated columns I'd like to create:
Store Name | AVG of Monthly Volume | Upper Threshold | Lower Threshold |
Question : The number 80.92 at the bottom of the below picture is what I want to get for the calculated column "AVG of Monthly Volume". Basically calculate the Sum Quantity of each Month then take Average of all of them and show the result in "AVG of Monthly Volume"
I want it to be a caculated column because I need it for the below calculated columns "Upper Threshold" & "Lower Threshold":
Upper Threshold = SWITCH ( TRUE (), Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 1.2 , Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.1 , Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 1.2 )
Lower Threshold = SWITCH ( TRUE (), Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 0.8 , Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.9 , Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 0.8 )
Can anyone please advise how I can do that? Thank you so much!
Solved! Go to Solution.
Hi @trdoan ,
To create a calculated column in tbl_FullData table.
Yearmonth = YEAR('tbl_FullData'[Posting Date])*100+MONTH('tbl_FullData'[Posting Date])
Then update the average column as below.
AVG of Monthly Volume = CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) ) / CALCULATE ( DISTINCTCOUNT( tbl_FullData[Yearmonth] ), FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] ),VALUES(tbl_FullData[Posting Date].[Year]))
Regards,
Frank
Hi @trdoan ,
Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.
Regards,
Frank
Hi @v-frfei-msft ,
Here is the link to my sample data.
I'd like to create the following 3 calculated columns in the "tbl_Threshold" (though the Upper Threshold & Lower Threshold I already knew how to get them):
Store | AVG Volume | Upper Threshold | Lower Threshold |
A | ? | AVG Volume * 1.1 | AVG Volume * 0.9 |
H | ? | AVG Volume * 1.05 | AVG Volume * 0.95 |
E | ? | AVG Volume * 1.2 | AVG Volume * 0.8 |
C | ? | AVG Volume * 1.15 | AVG Volume * 1.15 |
Taking A as an example using the sample file, it has the following quantity data:
Store | Order Quantity | Year | Month |
A | 7 | 2017 | July |
A | 65 | 2017 | October |
A | 220 | 2017 | November |
A | 434 | 2017 | December |
A | 57 | 2018 | February |
A | 101 | 2018 | March |
A | 188 | 2018 | April |
A | 103 | 2018 | May |
A | 195 | 2018 | June |
A | 157 | 2018 | July |
A | 169 | 2018 | August |
A | 22 | 2018 | November |
So the Calculated Columns should return the following:
Average Volume | Upper Threshold | Lower Threshold |
=(7+65+220+434+57+101+188+103+195+157+169+22)/12 =143.17 | =143.17* 1.1 = 157.48 | =143.17*0.9 = 128.85 |
I just don't know how to get Power BI to calculate Net Volume for each month (like what you can see from the first table) then take Average of all those Monthly Net Volumes (like the second table above) to finally produce the 143.17.
Please can you show me how I can return 143.17 in the calculated column 'tbl_Threshold'[Average Volume] ?
Thank you so much!
Hi @trdoan ,
To use the formula to get the correct AVG of Monthly Volume.
AVG of Monthly Volume = CALCULATE(SUM(tbl_FullData[Order Quantity]))/CALCULATE(DISTINCTCOUNT(tbl_FullData[Posting Date].[Month]))
Then add another two calculated columns.
Upper Threshold = SWITCH ( TRUE (), Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 1.2 , Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.1 , Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 1.2 , Threshold[Store] = "C", Threshold[AVG of Monthly Volume] * 1.15)
Lower Threshold = SWITCH ( TRUE (), Threshold[Store] = "A", Threshold[AVG of Monthly Volume] * 0.8 , Threshold[Store] = "E", Threshold[AVG of Monthly Volume] * 1.9 , Threshold[Store] = "H", Threshold[AVG of Monthly Volume] * 0.8 , Threshold[Store] = "C", Threshold[AVG of Monthly Volume] * 1.15 )
Pbix as attached.
Regards,
Frank
Hi @v-frfei-msft , thanks for your advice! However, I noticed something strange.
The formula only returns correct result for Store A because A accidentally has 12 months worth of data and the DISTINCTCOUNT formula, as I take it, counts distinctively the number of month in the Posting Date column. There are certainly 12 months in a year which makes the formula divide by 12 for every case, even though some Stores might have more or less months.
In the above picture, Store C only has 7 months worth of data and the 686 / 7 would be 98, not 57.17 (which is 686/12). So I doubt the DISTINCTCOUNT will work in this formula.
Is there a way to distinctively count only the months in which the store has data?
Can you please advise ? Thank you! Thank you!!!!
Hi @trdoan ,
Update the formula as below.
AVG of Monthly Volume = CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) ) / CALCULATE ( DISTINCTCOUNT ( tbl_FullData[Posting Date].[Month] ), FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] ) )
Regards,
Frank
Hi @v-frfei-msft ,
Thank you for your help again, the formula still doesn't work out completely.
In the above screenshot, there are 11 months for H and this formula only counts 7 distinctive months (June, July, August, September, October, November, and December 2017) and ignores the 4 repeating months (July, August, September, and December 2018).
Can you please help me write another formula that can take both month and year or whatever you see fit to fix this problem? Thanks a lot!!!
Hi @trdoan ,
To create a calculated column in tbl_FullData table.
Yearmonth = YEAR('tbl_FullData'[Posting Date])*100+MONTH('tbl_FullData'[Posting Date])
Then update the average column as below.
AVG of Monthly Volume = CALCULATE ( SUM ( tbl_FullData[Order Quantity] ) ) / CALCULATE ( DISTINCTCOUNT( tbl_FullData[Yearmonth] ), FILTER ( tbl_FullData, Threshold[Store] = tbl_FullData[Store Name] ),VALUES(tbl_FullData[Posting Date].[Year]))
Regards,
Frank
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |