Multiple filters inside a measure for coefficient of variation calculation

[Anonymous]

Hi! I'm doing a report that needs to have a visual card showing the coefficient of variation of the statistical sample. The formula I'm using for that is in Excel:

SI.ERROR(RAIZ(EXP(6,14777-0,92778*LN('PARÁMETRO')));"")

ive traslated that formula to dax like this:

CV =
IFERROR(
SQRT(
EXP(6.14777 - 0.92778 * LN([N]))),"")*100

The formula works fine. The problem is that I need it to work with different values. Basically, the value of "N" has to change according to the filters that the users apply, in order to calculate the coefficient of variation for that population size. The filters that users can use are three: age (edad), year(anio), and trimester.(trimestre)

Ive tried to create a calculated measure for N that respects this filters, but im stucked. Ive tried in different ways. 

First, by using the function SELECTEDVALUE and FILTER 
fexp_variable_3 =
VAR AnioSeleccionado = SELECTEDVALUE(Etoi_Apilada[anio])
VAR EdadSeleccionada = SELECTEDVALUE(Etoi_Apilada[edad])
VAR TrimestreSeleccionado = SELECTEDVALUE(Etoi_Apilada[trimestre])
RETURN
CALCULATE(
SUM(Etoi_Apilada[fexp]),
FILTER(
ALL(Etoi_Apilada),
Etoi_Apilada[anio] = AnioSeleccionado &&
Etoi_Apilada[edad] >= EdadSeleccionada &&
Etoi_Apilada[trimestre] = TrimestreSeleccionado
))

This didn't work. The filters for year and trimester seem to be working, but the age filter didn't. So I've tried to use a separate measure for each filter. 

Filtro_Edad =

VAR EdadSeleccionada = SELECTEDVALUE(Etoi_Apilada[edad])

RETURN

CALCULATE(

    COUNTROWS(Etoi_Apilada),

    FILTER(

        Etoi_Apilada,

        Etoi_Apilada[edad] >= EdadSeleccionada

    )

)

And then apllied to a new measure: 

fexp_variable_3 =

VAR FiltroEdad = [Filtro_Edad]

VAR AnioSeleccionado = [filtro_anio]

VAR TrimestreSeleccionado = [Filtro_trim]

RETURN

CALCULATE(

    SUM(Etoi_Apilada[fexp]),

    FILTER(

        ALLSELECTED(Etoi_Apilada),

        Etoi_Apilada[anio] = AnioSeleccionado &&

        Etoi_Apilada[trimestre] = TrimestreSeleccionado &&

        Etoi_Apilada[edad] >= FiltroEdad

    )

)

This didn't work either. Any suggestions?

P.S.: The column "fexp" represents the expansion factor of the sample, which weights the statistical base. Usually, I use CALCULATE with a SUM function to consider fexp column as a whole (representing the entire population) and then establish some filters over it.

[Anonymous]

PS: 
ive tried to only use the filter for the age column, but for some reason it doesnt change the values of "fexp" column when i use the slice 

[v-weiyan1-msft]

Hi @Anonymous ,

Calculated table is different from Calculated column, you can create a Calculated table by selecting "New table" in the picture below.

From the screenshot you provided, the style of your Age slicer is "Between".

Therefore, your code should be modified slightly as follows.

fexp_variable_3 = 
VAR YearSelected =
    SELECTEDVALUE ( 'YearTable'[year] )
VAR AgeSelected =
    MAX ( 'AgeTable'[age] )
VAR QuarterSelected =
    SELECTEDVALUE ( 'QuarterTable'[quarter] )
RETURN
    CALCULATE (
        SUM ( 'Table'[fexp] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year] = YearSelected
                && 'Table'[age] >= AgeSelected
                && 'Table'[quarter] = QuarterSelected
        )
    )

Result is as below.

When only use the filter for the age column, Result is as below.

fexp_measure = 
VAR Age_Sel =
    MAX ( 'AgeTable'[age] )
RETURN
    CALCULATE ( SUM ( 'Table'[fexp] ), FILTER ( 'Table', 'Table'[age] = Age_Sel ) )

For further detail, please find attachment.
For more about the Calculated table you may refer to:

Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Yulia Yan

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

[Anonymous]

hi @v-weiyan1-msft, thank you again for your answer and your time! 

I followed your suggestion but still cant make this work. I dont know if there is something wrong in my sintaxis. 

First i create an age table: 

then i created the filter 

but i still get blank value as result

is there any error you can see? 

Thank you a lot in advance!

[v-weiyan1-msft]

Hi @Anonymous ,

Based on the screenshots and formulas you provided, it looks like there is nothing wrong. You can try downloading the attachment in my last reply to check what's different from yours that's causing the problem.

Best Regards,
Yulia Yan

[Anonymous]

Hi @v-weiyan1-msft! Thank you for your answer. Yes! thats exactly what i expected, a sum of all fexp column values according to the filters that the users selects. Anyway, I still have some issues here trying to solve this.

Your suggestion is similar to what I've tried before. The difference lies in that you first created a calculated table for each column before applying the filters in a differente measure. I'm not sure what a calculated table is; are you referring to a calculated column? Because when I tried to create it, I got this error:

Ive tried doing it in calculated measures: 

but then, the coefficient variation formula throws an error: 

About the semantic model, this is an open data statistic database. Year, Quarter, Age and Fexp are all columns of the same database. 

i readapt your solution to the database, without using calculated measures for the columns year, quarter and age: 

when i used this new "fexp_variable_test" measure in the variation coefficient calculation, the filters works for quarters and years, but not for age.

And when i tried to see the result of the filter in a visual card, it returns blank values. I just dont understand why. 

Thanks in advance for your time! 

[v-weiyan1-msft]

Hi @Anonymous ,

I am not sure how your semantic model looks like.
Based on the information you provided, here is the dummy data I created.

It seems that you are trying to create a Measure that satisfy the conditions selected by three slicers.
Based on my understanding, Please try the following steps:
1.You can create Calculated table.

YearTable = VALUES('Table'[year])

AgeTable = VALUES('Table'[age])

QuarterTable = VALUES('Table'[quarter])

2. Use the following code to create a Measure.

fexp_variable_3 = 
VAR YearSelected =
    SELECTEDVALUE ( 'YearTable'[year] )
VAR AgeSelected =
    SELECTEDVALUE ( 'AgeTable'[age] )
VAR QuarterSelected =
    SELECTEDVALUE ( 'QuarterTable'[quarter] )
RETURN
    CALCULATE (
        SUM ( 'Table'[fexp] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year] = YearSelected
                && 'Table'[age] >= AgeSelected
                && 'Table'[quarter] = QuarterSelected
        )
    )

The fields of the three slicers are from the corresponding Calculated table.
When you select "2024", "Q4", "13" in the slicers, Result is as below.

Is this the result you expect?
If I've misunderstood you, can you share sample data and the results you are hoping for? Show it as a screenshot or excel. Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,
Yulia Yan

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