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
DebbieE
Community Champion
Community Champion

Obtener el valor Medio de una lista (Promedio no funciona)

AverageCas.JPG

Tengo un SUM(cas) como medida

Y también tengo una medida PROMEDIO (Cas) como arriba.

Y necesito mostrar el valor medio de la suma que debe ser alrededor de 347

Ya tengo un filtro aplicado en los años para conseguir los años anteriores en la tabla de fechas

Pero no se me puede llegar a DAX que hace esto

Con el tiempo quiero mostrarlo en una tarjeta por lo que tendrá que tener el filtro aplicado a él para el número de años, pero luego mostrar ese valor medio de Cas

7 REPLIES 7
DataInsights
Super User
Super User

@DebbieE,

Prueba estas medidas. [Valor medio] promedia las sumas anuales.

Sum Cas = SUM ( AverageTest[Cas] )

Mean Value = 
VAR vBaseTable =
    ADDCOLUMNS ( VALUES ( Dates[Year] ), "tmpSumCas", [Sum Cas] )
VAR vResult =
    AVERAGEX ( vBaseTable, [tmpSumCas] )
RETURN
    vResult

DataInsights_0-1606585118593.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Im getting 69.84 not 347. Is there anyway to add into the vbase table a filter on Area = "UK" and a BaseFlag = true in the date table?  This is because there are only 5 years used for this Baseline average.

 

I ttied adding these in a CALCULATE to 

vResult but it hasnt made a difference

@DebbieE,

Prueba esto, con una cortadora para Area.

Mean Value = 
VAR vBaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Dates[Year] ),
            Dates[Base Flag] = "True"
        ),
        "tmpSumCas", [Sum Cas]
    )
VAR vResult =
    AVERAGEX ( vBaseTable, [tmpSumCas] )
RETURN
    vResult





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




So nearly there. This  works perfectly as my card. However  I use this in some more DAX that wants to use it as a single value but the filter for Dates are being applied for this one too. Is there any way I can disconnect this value so other subsequent DAX can use it just as a number?

 

This is the next bit of DAX its being applied in

Targets for column chart =
VAR LatestYr =
CALCULATE(
MAX('Date'[Year]),
ALLSELECTED('Date'[Year])
)

VAR EarliestYr =
CALCULATE(
MIN('Date'[Year]),
ALLSELECTED('Date'[Year])
)
VAR breakdownPart =
DIVIDE([Target] - [Baseline Average], LatestYr - EarliestYr)

RETURN
[Baseline Average] + breakdownPart * (MAX('Date'[Year]) - EarliestYr)
 
And I create a clustered column chart with dates from 2018 to 2033
 
 

@DebbieE,

Me alegra oír que la medida funciona para la tarjeta visual.

En cuanto a la segunda solicitud, suena como si desea que el usuario seleccione los años en la segmentación de fecha, en lugar de usar Dates[Base Flag] á "True". Si ese es el caso, necesitaría una medida separada ya que la medida que proporcioné está diseñada para usar Fechas[Bandera base] - "Verdadero".





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Im not quite sure. the measure is correct with the Base flag = true. But the second measure needs to use this measure as it its just a number, completely independant on all the filters etc set for that measure. It works perfectly if you create a measure of say = 346. So I need the lower level one to work in the 2nd of DAX as if it was a hardcoded value

 

@DebbieE,

Puede cambiar el contexto del filtro para usar los años de segmentación de datos. En el DAX posterior, agregue una variable:

VAR BaselineAvg =
CALCULATE (
    [Baseline Average],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Year] >= EarliestYr
            && 'Date'[Year] <= LatestYr
    )
)

Utilice esta variable BaselineAvg en su cálculo:

VAR breakdownPart =
DIVIDE([Target] - BaselineAvg, LatestYr - EarliestYr)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Top Solution Authors