PBI Measure Issue

[ShaneOC12]

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

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

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.