Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |