DAX

[sanju24566]

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.

[NikhilChenna]

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!

[sanju24566]

thanks but isnt this possible with measure

[NikhilChenna]

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!

[sanju24566]

Hello Nikhil 

Thanks for the solution. But the results are not what I expected

I wanted to know the No. of Site Counts >=1.5 yearwise. It is showing me the Total Count of Site Code

[sanju24566]

This is what I want to achieve. Can I use a related function anywhere?

[NikhilChenna]

Hi @sanju24566 ,

I think we already got the solution. you just have to visual level fiter to this visual.

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)

Add this calculated column as visual level filter. You should get the desired output.

Regards,

Nikhil Chenna

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

[sanju24566]

i have already used it in the visual above with Year in the axis. But the Count is coming 19 which is Total Count of Site Code

[NikhilChenna]

Hi @sanju24566 ,

Can you provide me this below excel file with few entires. I will try again and let you know in few mintues. 

Regards,

Nikhil Chenna