Forum Discussion
Average calculation
Team,
I have a requirment of calculating average balances from two columns in same table looks like below my sample data
Could you someone help me to get this report out
i strugling to get this in days but no idea how to proceed.
Advance Thanks
6 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Questions - What is the reason Aug is left over, What is the logic to consider respective cells ! without Logic we may not be able to build the DAX.
For a regular AVERAGE you can use this function but further logic you need to define and let me know to give you more clear DAXaverage = IF(NOT(ISBLANK([Total BCOGS])),AVERAGEX(DATESINPERIOD(dimDate[Date],SELECTEDVALUE(dimDate[Date]),6,MONTH),[Total BCOGS]),BLANK())VijayP
The actual requirement is, Average COGS will available only for Jan-Jun for every year,
Average calculation is needed for future 6 months, if period is less than 6 months then remaining months need to take from average COGS column
Eg. For Jan - The average values from Feb-July
for Feb - the average values from Mar-Aug
....
for Jul - The average values from Aug-Dec - 5 Months and Jan month AVGCOG for 1 month
For Aug - The average values from Sep-Jan - 5 Months and Feb month AVGCOG for 1 month
...
Dec The Average values are Jan-May for 5 months and Jun month AVGCOGS for 1 month
i hope explained your query
I have created new table tried working on the average , but still I am not convinced with the Logic
New Table = FILTER(SUMMARIZE(dimDate,dimDate[Date],"BCOGS",BudgetAvgTrans[Total BCOGS],"AVGCOGS",SUM(BudgetAvgTrans[AVGCOGS])),not(ISBLANK(SUM(BudgetTrans[BCOGS]))) && NOT(ISBLANK(SUM(BudgetAvgTrans[AVGCOGS]))))Check the file attached and let me know! Share your Kudos
