Calculation

[Anonymous]

Hi,

I want to create a measure to get the smallest average taken from a list.

Two screenshots below, show the data format and the visual I have taken.

I now want to use a measure to get the '0.93' value so that I can use it on a card.

I'm unsure how to do so.

Any help would be appreciated.

Regards,
CameronTCD

[V-lianl-msft]

Hi @Anonymous ,

You could use "MINX" function to get the  minimum value in measure. Please try the DAX like this:

Measure = MINX(table,[AVG_value])

Best Regards,

Liang

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

[Anonymous]

Hi @V-lianl-msft 

I have run this calculation however it just gives me the average of '1.6', instead of the lowest average by check

[V-lianl-msft]

Hi @Anonymous,

 

Measure is related to the current context, please note whether the data is filtered or use ALL function.

Measure = MINX(ALL(table),[AVG_value])

If the problem persists,could you please share sample data and DAX formula?

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

[Anonymous]

Hi @V-lianl-msft 

I can't seem to find what I'm looking for.

Please see the sample attached: https://www.dropbox.com/s/euz78g6444lfmrl/Compliance%20Reflection%20-%20sample.pbix?dl=0

[V-lianl-msft · Accepted solution]

Hi @Anonymous

 

I took a look at your pbix and mistakenly thought you had calculated the average.
You need to create a calculated column to calculate the average:

average_col = CALCULATE(
                AVERAGE('Scores by check (2)'[Value]),
                FILTER(ALLSELECTED('Scores by check (2)'),
                        'Scores by check (2)'[Attribute]=EARLIER('Scores by check (2)'[Attribute])))

Then create a measure to calculate the minimum average:

Measure = MINX('Scores by check (2)','Scores by check (2)'[average_col])

It works like this:

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

[Anonymous]

Hi @V-lianl-msft 

Thank you! 

Works a dream.

Now I've got a follow-up question.

I'm using the 'Enlighten Data Story' visual. 

Is there any way to link the score to the check?

My hope here is that it would say that it's x score for x check. 

Does this make sense?

Many thanks for your help thus far.

[CoreyP]

Will this work for what you're trying to do?

DOC//SigMatches AVG = CALCULATE( AVERAGE('Table'[Score]) , 'Table'[Check] = "DOC//SigMatches" )

[Anonymous]

Hi @CoreyP 

That will work in this instance however this will be a live report with new data being pushed through periodically, meaning that the lowest average will change from DOC//SigMatches