topic Re: Compute Average Cost in Power BI (Total Actuals / Count D Sites) in DAX Commands and Tips

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

A22MATIC — Mon, 01 Sep 2025 23:12:30 GMT

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.

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

bhanu_gautam — Tue, 02 Sep 2025 05:41:01 GMT

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]

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

sanalytics — Tue, 02 Sep 2025 10:05:13 GMT

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

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

A22MATIC — Tue, 02 Sep 2025 13:21:39 GMT

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.

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

A22MATIC — Tue, 02 Sep 2025 13:30:52 GMT

Hello, Thanks for the response.

Unfortunately, that did not work.

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

sanalytics — Tue, 02 Sep 2025 13:56:29 GMT

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

Regards

sanalytics

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

Gabry — Tue, 02 Sep 2025 14:43:28 GMT

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?

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

A22MATIC — Tue, 02 Sep 2025 16:50:01 GMT

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?

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

A22MATIC — Tue, 02 Sep 2025 16:49:28 GMT

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?

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

A22MATIC — Tue, 02 Sep 2025 17:06:26 GMT

Dropbox Power BI file

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

A22MATIC — Tue, 02 Sep 2025 17:07:07 GMT

Dropbox Power BI file

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

Gabry — Tue, 02 Sep 2025 18:06:52 GMT

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

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

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

A22MATIC — Tue, 02 Sep 2025 18:19:29 GMT

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