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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Matej
Helper I
Helper I

AVR from visual in the measure

Hi pros, 

 

Hopefully someone will be able to help me out here. I have a first page which uses slicer to select one client and then proceeds to a different page. On here, I have this visual which shows sites with total spend and average accros that chosen client. What I would like to do is to create a measure that calculates AVR for that one client accros all of their sites - so in this case the AVR will be £5,132 (just in measure so I can use it for further calculations such as difference between total site spend and AVR spend accros all sites). 

Matej_0-1691147206858.png

I can't just use simple AVERAGE as the total spend itself is a measure:

_Revenue £ =
CALCULATE(
    [Amount (Dr/Cr)],
    'Chart of Accounts'[Name] = "510000 Sales : Trading"
    )
    +0
 
and Average does not like the measure inside. I have tried multiple forms of AVERAGEX but can't land on the desired number. When thinking about the problem it feels like I'm missing something relatively easy, but I just can't figure out what it is. Any help or recommendations would be highly appreciated. 

Cheers

 

2 REPLIES 2
Matej
Helper I
Helper I

Hi @MFelix ,

Thanks for the reply. I have tried your solution and it works on the base level. I was able to get the AVR value for the group of sites. Problem is that when I highlight any site the AVR value is same as Total Revenue. Therefore my end goal, to calculate "Revenue of specific site" - "AVR of the group of sites" does not work as it always returns 0 (two values are same). I would like to use this calculation to point out how much over/under is specific site compared to the group AVR in spending (what is the potential savings). 

Any idea of how to get to the end goal here? 
Many thanks 

MFelix
Super User
Super User

Hi @Matej ,

 

Try the following code:

 

 

Average Value =
AVERAGEX (
    SUMMARIZE (
        Table,
        Table[Sites],
        "_Revenue", [_Revenue £]
    ),
    [_Revenue]
)

 

Be aware that this is generic code and you may need some changes if you are not using slicers or filter to select the specific customers.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.