Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gheb_Gabriela
Frequent Visitor

AVERAGE FORMULA

Hi all,

I need help with something, if you have any idea:

 

I have used the below formula to make a measure for what I need in the report and it works for me, but is there any way to can add average at this formula to can calculate only the totals to be average?

 

Gheb_Gabriela_0-1659965335276.png

To be clear enough I need only 20.29% and 17.90%,19.74% and so on to be average instead of Sum. 

Gheb_Gabriela_3-1659965678771.png

Thanks a lot!

 

4 REPLIES 4
YalanWu_test
Helper I
Helper I

Hi, @Gheb_Gabriela ;

You could create a measure.

 measure=if(hasonevalue([Cate]),[PAYOUT MEASURE],AVERGEX('TABLE',[PAYOUT MEASURE])

 By HASONEVALUE, [Cate] should be the column in your matrix column field. 

If not right.Can you post sample data as text and expected output?

 

Best Regards,

Hi @YalanWu_test the formula is not working 

 

Ok, so the my table in excel is looking like this 

Gheb_Gabriela_0-1660208391509.png

The formula used for this ones in excel was : 

Gheb_Gabriela_1-1660208422812.png

In Power BI I have done like this : Sum of the column i needed for my result and count of the same column. After that, to get the exact results from my Excel Table i have used the below formula and it is working perfect. I have the same results for the columns but not the average for totals. I need the average to work vertically or horizontally, doesn't matter. Below the formula from Power BI

Gheb_Gabriela_2-1660208514179.png

This are my results on matrix in Power BI. Also I think, maybe it is a problem because I can't replace blanks in the matrix to be 0.0%. I have tried with "+0", I have tried with count formula, nothing it's working.

Gheb_Gabriela_3-1660208599837.png

Thanks a lot, appreciate your time to answer.!

 

 

 

 

amitchandak
Super User
Super User

@Gheb_Gabriela 

Try like , if they are columns

 

divide(sum(Table[Bonus_payout]), Sum(Table[Bonuspayout count]) )

 

if they are measure avg should already be  correct

 

 

divide([Bonus_payout],[Bonuspayout count])

 

or use like this using columns on row and column of matrix

 

averaged(Summarize(Table, Table[row column], Table[Column Column), calculate(divide([Bonus_payout],[Bonuspayout count]))

@amitchandak thanks a lot for your solutions but it's not working. With the first measure, if i am trying to apply it, doesn't find my measures ( bonus payout and bonus payout count) and the second one, give me the same results.

In excel my formula is this one:

Gheb_Gabriela_0-1660029491160.png

In power Bi first i have sum the column, after that i've made the count for the same column and after that i've used the iferror in Power BI and the result is good, same with my analysis from Excel, but now, I only need the average for totals... 

 

I don't know why it's not working. I can't make the average for the same column twice. It is not what I need to get the correct results for my report.

 

Thank you in advance!

 

Have a great day!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.