Forum Discussion

sanju24566's avatar
sanju24566
Regular Visitor
3 years ago

DAX

 

Site Code appearing multiple times

 

Hello

I Need to find here the following No. of sites codes >= 1.5 average value
So Above are the Site Codes in 2nd Column for different Toxin Names at different year . So One Site code is appearing multiple times. And there is Values in last column. I want to create a measure which groups or remove duplicate of Site Codes and alonside give me the Average of the Values column where the average of Values is >= 1.5  for each Site Code . 

So when I use in a visual I could get No. of sites codes >= 1.5 average value. When used with a Year in Axis.

Data Protection
Need Help
Show and Tell
Tips and Tricks

8 Replies

  • NikhilChenna's avatar
    NikhilChenna
    Skilled Sharer
    3 years ago

    Hi sanju24566 , I think this is the solution.

     

    1. Create a summarized table in Power BI, go to the modelling tab and click on the new table option as shown in the below screenshot.

     

    Summarize_table =
    SUMMARIZE(
        'table1',
        'table1'[Site Code],
        "AvgValue",AVERAGE('table1'[value])
    )
     
    2. After this you can create calculated column to for your condition of value 1.5 average value.
    calculated column = IF('table1'[AvgValue]>=1.5,1,0)
     
    So now you filter 1 you will be able to see the site codes where the avg. if the value is greater and equal to 1.5.
     
    Regards,
    Nikhil Chenna
     
    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!
      • NikhilChenna's avatar
        NikhilChenna
        Skilled Sharer
        3 years ago

        Hi sanju24566 ,

         

        Try using Summarize with measure and let me know. Creating a summarized table is suitable in this situation.

         

        Regards,

        Nikhil Chenna

         

        Appreciate with a Kudos!! (Click the Thumbs Up Button)
        Did I answer your question? Mark my post as a solution!