Forum Discussion
PBI Measure Issue
Hi,
I have an issue where I'm calculating the Overall Average Monthly Sales, which at the moment is 337 and the total returns (RGA's) per month which varies month to month. I then divide the Total RGA's by the Average Monthly Sales to get the RGA % per month. The problem I'm having is when I put this measure in as a line y-axis in my bar chart, the Average Monthly Sales is calculated for every individual month, so it basically just give me the total sales for each month where I want it to just stay as the Overall Average Monthly Sales (337 sales per month). I would just divide total RGA's by 337 but this number could change daily so I want it calculated using a measure. Ill include the measures I'm using below along with some photos of the graphs.
Total RGA's = COUNT('RGA: Evaluation'[RGA No])
------------------------------------------------------------------------------------------------
Overall Average Monthly Sales =
AVERAGEX(
KEEPFILTERS(VALUES('RGA: Date table'[Month Year])),
CALCULATE(SUM('RGA: US Sales Data'[Quantity]))
)
------------------------------------------------------------------------------------------------
RGA% =
DIVIDE([Total RGA's], [Average Quantity Per Month])
Hi ShaneOC12
Please try to use the following DAX to create a measure to represent "Overall Average Monthly Sales", I hope it works.
Overall Average Monthly Sales = CALCULATE( AVERAGEX( VALUES('RGA: Date table'[Month Year]), CALCULATE(SUM('RGA: US Sales Data'[Quantity])) ), REMOVEFILTERS('RGA: Date table'[Month Year]) )Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi ShaneOC12
Please try to use the following DAX to create a measure to represent "Overall Average Monthly Sales", I hope it works.
Overall Average Monthly Sales = CALCULATE( AVERAGEX( VALUES('RGA: Date table'[Month Year]), CALCULATE(SUM('RGA: US Sales Data'[Quantity])) ), REMOVEFILTERS('RGA: Date table'[Month Year]) )Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
I cannot understand your desired result. Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
