Compute Average Cost in Power BI (Total Actuals / Count D Sites)

[A22MATIC]

Hello,

I'm new to Power BI. The forums have been beneficial. I have encountered an issue that I have been unable to find a clear answer for or resolve.

I am trying to calculate the Average Spend $ for the total Count D Sites 10 (Total Actuals by Site / Total Count D of Sites). Can anyone assist or point me in the right direction? Thanks.

[sanalytics]

@A22MATIC 
Please use below code for your measure

Average Acts by Total Sites = 
VAR _Numerator = SUM( 'Table'[Total Actuals - Convention Spend] )
VAR _Denominator = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[Site Number]), ALL()
)
VAR _Result = 
DIVIDE(_Numerator,_Denominator,0)
RETURN
_Result

below screenshot

Attached pbix file for your reference

Hope it hleps

Regards

sanalytics

[A22MATIC]

Hello, Thanks for the response.

Unfortunately, that did not work. I've tried to include all the screenshots below. I thought maybe it's the data format (General vs. Whole Number). Not sure why it's not working. I don't see a way to attach the Power BI file, but if there's a way, please let me know.

[sanalytics]

@A22MATIC Please share some dummy data along with your Power BI model. It will help us to work on..

Regards

sanalytics

[A22MATIC]

Thanks. I see the issue. You are correct. The formula works. Do you know how to make it work with a slicer? My end users will filter on their Project, and will need to see the averages that way.

Your formula works, but I have a slicer, and when it's selected, the Average for Count D isn't working. I'm struggling trying to attach the Power BI file. Can you please let me know how I can do that?

[A22MATIC]

Dropbox Power BI file 

[bhanu_gautam]

@A22MATIC 

Create a Measure for Total Spend:

Go to the "Modeling" tab.
Click on "New Measure".
Enter the following DAX formula to calculate the total spend

Total Spend = SUM('YourTableName'[Total Actuals - Convention Spend])

Then one for

Count of Distinct Sites = DISTINCTCOUNT('YourTableName'[Site Number])

And last for average

Average Spend per Site = [Total Spend] / [Count of Distinct Sites]

[A22MATIC]

Hello, Thanks for the response.

Unfortunately, that did not work.

[Gabry]

Hey @A22MATIC ,

why are you saying it's not working?
Sum of ACTS = 27637816

Number of sites= 250

Average= 27637816/250=110551

Do you want to display the same average in all the rows? Regardless of the site in the current row?

[A22MATIC]

You are correct. The formula works. Do you know how to make it work with a slicer? My end users will filter on their Project, and will need to see the averages that way.

The issue is I have a slicer, and when it's selected, the Average for Count D isn't working. I'm struggling trying to attach the Power BI file. Can you please let me know how I can do that?

[Gabry · Accepted solution]

I looked at the file, but I don’t quite understand what you want to do. If you use the filter in the top right corner of the canvas, it calculates the average for the selected project, right? Are you asking for the overall average regardless of the project selected?

You can achieve this with the following formulas:

Average Acts by Total Sites = 
VAR _Numerator = CALCULATE(SUM( 'Table112'[Actuals] ), ALL(Table112[Proj Def & Name]))
VAR _Denominator = 
CALCULATE(
    DISTINCTCOUNT( 'Table112'[Site]), ALL()
)
VAR _Result = 
DIVIDE(_Numerator,_Denominator,0)
RETURN
_Result

Or

Average Acts by Total Sites = 
VAR _Numerator = CALCULATE(SUM( 'Table112'[Actuals] ), ALL(Table112))
VAR _Denominator = 
CALCULATE(
    DISTINCTCOUNT( 'Table112'[Site]), ALL()
)
VAR _Result = 
DIVIDE(_Numerator,_Denominator,0)
RETURN
_Result

Oh I think maybe I understood what you want to do, try this and let me know:

Average Acts by Total Sites = 
VAR _Numerator = SUM( 'Table112'[Actuals] )
VAR _Denominator = 
CALCULATE(
    DISTINCTCOUNT( 'Table112'[Site]), ALLSELECTED()
)
VAR _Result = 
DIVIDE(_Numerator,_Denominator,0)
RETURN
_Result

[A22MATIC]

YES!! That is precisely what I'm trying to do!!

So sorry it took so long, and thank you for your patience with this newcomer!

Average Acts by Total Sites = 
VAR _Numerator = SUM( 'Table112'[Actuals] )
VAR _Denominator = 
CALCULATE(
    DISTINCTCOUNT( 'Table112'[Site]), ALLSELECTED()
)
VAR _Result = 
DIVIDE(_Numerator,_Denominator,0)
RETURN
_Result

[A22MATIC]

Dropbox Power BI file