Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

DAX FOR TOP 3

Link to sample PBIX dear power bi community,

I am trying to write a DAX measure to calculate Average Sales that will be based on 'top 3 sites' only per each segment. For example, if I have 3 segments say Bar, hotel, cafe. I want just the top 3 outlets that contributed to the sales divided by the number of sites (3 will be the maximum). I have managed to do this with DAX. The only way I have managed to achieve this at the moment is to create an Average Sales table and joined it back to my lookup table Dim Sites. However, where it gets complicated is when I select a region (or regions) using a region slicer, If my top 3 sites don't belong to that region then I see fewer segments. So what I am trying to achieve is that for any selected region, display all the segment averages based on the top 3 sites of each segment in the region or regions selected.

 

amitchandak 

General Comment
Need Help
Tips and Tricks

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Unable to get the file from the link you provided, please try adding the attachment at the end.

    Maybe you can try this code:

    avg =
AVERAGEX(
    TOPN(
        3,
        SUMMARIZE( table, table[sites], "total", SUM( table[sales] ) ),
        [total],,
        desc
    ),
    [total]
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.