Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I used the following formula to calculate the percentile 0.25 over a measure:
Solved! Go to Solution.
Hi @Anonymous ,
Sorry for that I mixed up the measure and Unidades 12M and Unid x M2 12M , we can use the following measure to meet your requirement:
Limite Cuartil 1 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.25, 0 ) ),
[M12Value]
)
Limite Cuartil 2 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.5, 0 ) ),
[M12Value]
)
Limite Cuartil 3 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.75, 0 ) ),
[M12Value]
)
We suggest you to delete the the link you have shared if it contain any confidential information or it come from real data and it seems that you set edit permission within your shared link which is dangerous in a public forum.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
We can try to create a measure use following formula to meet your requirement:
Limite Cuartil 1 =
VAR t =
SUMMARIZECOLUMNS (
'Datos Finca'[AÑO MES],
"M12Value", CALCULATE (
SUM ( 'Datos Finca'[Unid] ),
DATESINPERIOD (
Calendario[FECHA],
LASTDATE ( Calendario[FECHA] ),
( -1 ),
YEAR
)
)
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.25, 0 ) ),
[M12Value]
)
If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lid-msft thank for the help.
The measure was not working on the report so here is the link of the data where on the sheet "Expected Result" it is the table that I am trying to create, a clasification by percentile using the measure "Unid x M2 12M". Hope you can help me.
Hi @Anonymous ,
Sorry for that I mixed up the measure and Unidades 12M and Unid x M2 12M , we can use the following measure to meet your requirement:
Limite Cuartil 1 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.25, 0 ) ),
[M12Value]
)
Limite Cuartil 2 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.5, 0 ) ),
[M12Value]
)
Limite Cuartil 3 =
VAR t =
ADDCOLUMNS (
GROUPBY (
FILTER (
ALLSELECTED ( 'Datos Finca' ),
'Datos Finca'[AñoMes] IN FILTERS ( 'Datos Finca'[AñoMes] )
),
'Datos Finca'[Sede]
),
"M12Value", [Unid x M2 12M]
)
VAR ti =
ADDCOLUMNS ( t, "index", RANKX ( t, [M12Value],, ASC ) )
RETURN
MAXX (
FILTER ( ti, [index] = ROUND ( COUNTROWS ( t ) * 0.75, 0 ) ),
[M12Value]
)
We suggest you to delete the the link you have shared if it contain any confidential information or it come from real data and it seems that you set edit permission within your shared link which is dangerous in a public forum.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |