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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Zbig62
Regular Visitor

problem with calculating the average using variables defining a range of values

Hello
I am a beginner DAX user and I have a serious problem with calculating the average using variables defining a range of values.
The problem is as follows:

Table: 'public Measurement'

1. average value for the entire range (10 results) as a separate column - works well

AV_N =

CALCULATE (

    AVERAGE ( 'public Measurement'[result]);

    FILTER (

        ALLSELECTED ('public Measurement');

        'public Measurement'[characteristicId] = MAX ( 'public Measurement'[characteristicId])

    )

)

 

The result of the measure is as follows

 

Zbig62_1-1713695699905.png

 


2. average value for 5 results as a separate column - it seems to work well but the value range is entered "manually"

 
AV5 =
CALCULATE (
     AVERAGEX('public Measurement';  'public Measurement'[result]);
   FILTER (
        ALLSELECTED ('public Measurement');
        'public Measurement'[characteristicId] = MAX ( 'public Measurement'[characteristicId])
    );
        'public Measurement'[number] >= 1 ; 'public Measurement'[number] <= 5  
)

 

Zbig62_0-1713695370898.png

average value from a range using variables (the measure doesn't work, why?)

 

Instead of constant values defining the average calculation range (1 and 5 ), I need to use variables.
the variable defining the final range of the value must be calculated, for example:
var stop = count('public measurement'[number]) (instead of the value 5)
How to use variables in such a measure?
Please help

 

1 ACCEPTED SOLUTION
Zbig62
Regular Visitor

welcome back
Thank you for your suggestions, it allowed me to locate the problem.
Instead of count() I used the countx() function and it works very well
thank you again

 

var stop_av = CALCULATE(
    COUNTX('public Measurement';'public Measurement'[number]);
    FILTER(
        ALLSELECTED('public Measurement');
        'public Measurement'[characteristicId] = MAX('public Measurement'[characteristicId])
    )
)

View solution in original post

5 REPLIES 5
Zbig62
Regular Visitor

welcome back
Thank you for your suggestions, it allowed me to locate the problem.
Instead of count() I used the countx() function and it works very well
thank you again

 

var stop_av = CALCULATE(
    COUNTX('public Measurement';'public Measurement'[number]);
    FILTER(
        ALLSELECTED('public Measurement');
        'public Measurement'[characteristicId] = MAX('public Measurement'[characteristicId])
    )
)

@Zbig62 

There shouldn't be any difference between COUNT and COUNTX. But your new solution made me understand that the number-Characteristicid combination in the source table is unique. 

 

Also COUNT/COUNTX should work properly only if [number] restarts from 1 for each characteristicid. 
note: the 'public Measurement'[number] >= start_av a gument is not needed in all cases. 

tamerj1
Super User
Super User

Hi @Zbig62 

as part of the debugging process you can create a measure Test = count('public measurement'[number]) and see if returns what you've expected it to return. 

Hello
Thanks for fast reaction.

 

This is where I have a problem, see the results in the table where the stop_av variable should have the value 10.
As a result, it obtains a bad average value
Please help me, I don't know how to do it

 

 

Zbig62_0-1713703364206.png

AV =
var stop_av = count('public Measurement'[number])
var  start_av = 1
var av =
CALCULATE (
     AVERAGEX('public Measurement';  'public Measurement'[result]);
   FILTER (
        ALLSELECTED ('public Measurement');
        'public Measurement'[characteristicId] = MAX ( 'public Measurement'[characteristicId])
    );
        'public Measurement'[number] >= start_av ; 'public Measurement'[number] <= stop_av
)
RETURN
av
 
Zbig62_1-1713703961379.png

 

 

@Zbig62 

Thank you for clarifying your problem one more time. That was clear to me already. My point was that it doesn't seem that you are aware of what var stop_av = count('public Measurement'[number]) returns. You only have an expectation that it should always return 10 while this is not true. In fact this expression will be evaluated within the filter context of evaluation. It n other words it might return different number for each number-characteristicid combination depending how many times this combination is repeated in the Source data table which I personally don't know for lack of information. But I can say it won't be 10 for all combinations unless all combinations have actually 10 rows. If you want to return the maximum [number] for each [caharastaristid] then you can try

var stop_av = CALCULATE ( MAX ( 'public Measurement'[number] ), FILTER (  ALLSELECTED ( 'public Measurement', 

        'public Measurement'[characteristicId]= MAX ( 'public Measurement'[characteristicId])
    )
 
However, that might not be it as you might face issues with AUTOEXIST. Along with ALLSELECTED, AUTOEXIST might produce problems given that [Result] column is a value column that is part of the filter context. 
If you can provide a sample file, it would be much easier to propose a totally different solution perhaps utilizing a NO CALCULATE approach that proofs to he much more reliably in such scenarios. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.