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.
Hi eveyone,
I have a table like in below with blue colour, based on the data in blue table,
Firstly I would like to calcuate the Quartile for each company, so for Q1, I am using formula in excel is: =QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),1)
Q2 is=QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),2)
Q3 is=QUARTILE(IF($A$1:$A$19=G1,$B$1:$B$19),3)
so it brings me the result of yellow table
Secondly, based on the result from yellow table, if the company rate less than or equals to Q1, the Level will be Q1;
If the company rate less than or equals to Q2, the level will be Q2;
if the company rate less that or equals to Q3, the level will be Q3;
and if the company rate large than Q3, the level will be Q4.
I would like to have a result for each company according to their rate (Green table, column N) in Power BI, for example
for N2, because it's company C, and its rate is 0.0166, whcih is less than I2, so it is Q1
for N3, because it's company A, and its rate is 0.0382, whcih is large than G4, so it is Q4
I would like to have a complete table such as Green table above with completed Level colunm, but could not figure out how to get this done in PowerBI.
I will appreciate if any one could help with this complex senario. Thanks!
Solved! Go to Solution.
Hi @engingee ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a level dimension table as below
2. Create two measures as below to get the quartiles by using DAX function PERCENTILEX.INC
Measure =
PERCENTILEX.INC (
FILTER ( 'Table', 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] ) ),
'Table'[Rate],
SWITCH (
SELECTEDVALUE ( 'Quartiles'[Level] ),
"Q1", 0.25,
"Q2", 0.5,
"Q3", 0.75,
"Q4", 1
)
)
Quartile =
SUMX (
VALUES ( 'Quartiles'[Level] ),
SUMX ( VALUES ( 'Table'[Company] ), [Measure] )
)
Best Regards
Hi @engingee ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a level dimension table as below
2. Create two measures as below to get the quartiles by using DAX function PERCENTILEX.INC
Measure =
PERCENTILEX.INC (
FILTER ( 'Table', 'Table'[Company] = SELECTEDVALUE ( 'Table'[Company] ) ),
'Table'[Rate],
SWITCH (
SELECTEDVALUE ( 'Quartiles'[Level] ),
"Q1", 0.25,
"Q2", 0.5,
"Q3", 0.75,
"Q4", 1
)
)
Quartile =
SUMX (
VALUES ( 'Quartiles'[Level] ),
SUMX ( VALUES ( 'Table'[Company] ), [Measure] )
)
Best Regards
Thanks @v-yiruan-msft ! I will sure double check and once it workes, I will accept as solution.
@engingee , check if this can help
https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |