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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CameronTCD
Helper I
Helper I

Calculation

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.

 

Xr4eKWr

 

mtT71oi

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

1 ACCEPTED SOLUTION

Hi @CameronTCD
 
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:
test_min.PNG
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

View solution in original post

8 REPLIES 8
V-lianl-msft
Community Support
Community Support

Hi @CameronTCD ,

 

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

 

Measure = MINX(table,[AVG_value])

 

 

test_min_value_measure.png

 

Best Regards,

Liang

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

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

Hi @CameronTCD,
 
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.

 

 

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

Hi @CameronTCD
 
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:
test_min.PNG
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

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?

 

jZJLmg5

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
Solution Sage
Solution Sage

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

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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