Forum Discussion
Simple Average
Hello! I have been using an AVERAGEX formula in power bi; however, I am noticing that this formula doesn’t work with breakdowns by category. I want to be able to take the average and divide it y total...
Hi,
I would like for my results below to total 19.06% instead of 27.24%. Since some months do not have values my formula is excluding those records. In this case it would be divided by 10 because there are only 10 months but it will change every month depending on the months selected. For instance next month it will be 12 months of data even if all months do not have values I need the data to divide by the full 12 months for the average.
Current formula:
AVG_Category_Breakdown =
AVERAGEX(SUMMARIZE(VALUES('download to use'[Month]),[Month],"ABCD",[Category_Breakdown]),[ABCD])
I would like for my results below to total 19.06% instead of 27.24%. Since some months do not have values my formula is excluding those records. In this case it would be divided by 10 because there are only 10 months but it will change every month depending on the months selected. For instance next month it will be 12 months of data even if all months do not have values I need the data to divide by the full 12 months for the average.
Current formula:
AVG_Category_Breakdown =
AVERAGEX(SUMMARIZE(VALUES('download to use'[Month]),[Month],"ABCD",[Category_Breakdown]),[ABCD])
Hello!
I have a measure in power bi that has the % breakdown for a particular category and after I get that I am attempting to create an average. This average however is excluding the months without values. Is there a way to get Power BI to include those months with no values? In the below example I am expecting to see 19.07 or close to that.
It may require a date_dim tbl that I didnt create in the file I am sharing but wanted to give an example quickly.
I have a measure in power bi that has the % breakdown for a particular category and after I get that I am attempting to create an average. This average however is excluding the months without values. Is there a way to get Power BI to include those months with no values? In the below example I am expecting to see 19.07 or close to that.
It may require a date_dim tbl that I didnt create in the file I am sharing but wanted to give an example quickly.
FYI, I can't open the file. What version did you author it with? I tried to open with latest (December) version of Power BI Desktop.
I just updated and confirmed this is on the Dec 2018 version of Power BI.
https://drive.google.com/file/d/1siXptcM3FGuvQ3VH4qbC3aBdgFUa0Dgl/view?usp=sharing
OK, I got it open.
I can't figure out why you want the average to be around 19. It looks correct as it is.
13.76/80.30 = 17.13%
How do you get the result you're expecting?
- I want the average to be a straight divide by the months so in this case it would be a straight divide by 12. It wouldn’t be 19.06 in this example it would be less. But if I selected on Jan-Oct it would be 19.06 or close to it. I need the calculation to do a straight average divided by each month. If we are looking at only Jan, Feb & March for instance and I select a filter for those months I would expect the average to be 13.06%.
I still don't understand what "straight divide by the months" means. What number are you dividing by 12?
Can you show the calculation for how you are getting 19.06% or 13.06% from the underlying column?
