Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
i have a lookup table bellow related to the Net sales data table:
Project No. | Product name |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
6 | F |
7 | G |
8 | H |
9 | I |
10 | J |
Net Sales Data:
Project No.YearNet Sales
1 | 2020 | 2.63 |
1 | 2021 | 4.5 |
1 | 2022 | 5.27 |
1 | 2023 | 6.16 |
1 | 2024 | 6.77 |
1 | 2025 | 6.77 |
1 | 2026 | 6.77 |
1 | 2027 | 6.77 |
1 | 2028 | 6.77 |
1 | 2029 | 6.77 |
2 | 2020 | 1.49 |
2 | 2021 | 3.56 |
2 | 2022 | 6.14 |
2 | 2023 | 6.37 |
2 | 2024 | 6.62 |
2 | 2025 | 6.78 |
2 | 2026 | 6.95 |
2 | 2027 | 7.12 |
2 | 2028 | 7.3 |
3 | 2023 | 6 |
3 | 2024 | 10.8 |
3 | 2025 | 10.8 |
3 | 2026 | 10.8 |
3 | 2027 | 10.8 |
3 | 2028 | 10.8 |
3 | 2029 | 10.8 |
3 | 2030 | 10.8 |
3 | 2031 | 10.8 |
3 | 2032 | 10.8 |
4 | 2021 | 0.45 |
4 | 2022 | 1.35 |
4 | 2023 | 2.25 |
4 | 2024 | 2.32 |
4 | 2025 | 2.39 |
4 | 2026 | 2.46 |
4 | 2027 | 2.53 |
4 | 2028 | 2.61 |
4 | 2029 | 2.69 |
4 | 2030 | 2.77 |
5 | 2022 | 0.45 |
5 | 2023 | 1.12 |
5 | 2024 | 2.25 |
5 | 2025 | 2.32 |
5 | 2026 | 2.39 |
5 | 2027 | 2.46 |
5 | 2028 | 2.53 |
5 | 2029 | 2.61 |
5 | 2030 | 2.69 |
5 | 2031 | 2.77 |
6 | 2023 | 0 |
6 | 2024 | 0 |
6 | 2025 | 1 |
6 | 2026 | 1 |
6 | 2027 | 1 |
6 | 2028 | 1 |
6 | 2029 | 1 |
6 | 2030 | 1 |
6 | 2031 | 1 |
6 | 2032 | 1 |
7 | 2020 | 0.64 |
7 | 2021 | 1.35 |
7 | 2022 | 1.64 |
7 | 2023 | 2.06 |
7 | 2024 | 2.76 |
7 | 2025 | 2.89 |
7 | 2026 | 3.02 |
7 | 2027 | 3.16 |
7 | 2028 | 3.32 |
7 | 2029 | 3.48 |
8 | 2020 | 1.26 |
8 | 2021 | 2.7 |
8 | 2022 | 2.76 |
8 | 2023 | 2.81 |
8 | 2024 | 2.87 |
8 | 2025 | 2.93 |
8 | 2026 | 2.99 |
8 | 2027 | 3.05 |
8 | 2028 | 3.11 |
8 | 2029 | 3.17 |
9 | 2021 | -2.4 |
9 | 2022 | 3.9 |
9 | 2023 | 6.35 |
9 | 2024 | 7.51 |
9 | 2025 | 10.22 |
9 | 2026 | 11.92 |
9 | 2027 | 15.22 |
9 | 2028 | 14.4 |
9 | 2029 | 13.61 |
9 | 2030 | 12.77 |
10 | 2020 | 6.58 |
10 | 2021 | 7.58 |
10 | 2022 | 7.1 |
10 | 2023 | 7.1 |
10 | 2024 | 7.1 |
10 | 2025 | 7.1 |
10 | 2026 | 7.1 |
10 | 2027 | 7.1 |
i would like to build a table like bellow. The Net Sales column shows the Total Sales of Product A regarding all its years of sales (long data above).
i used the measure bellow to get the 0.8 percentile:
Project No. | Product name | Net Sales | Percentile Net sales 0.8 |
1 | A | 59.18 | 6.77 |
2 | B | 52.33 | 7.018 |
3 | C | 103.2 | 10.8 |
4 | D | 21.82 | 2.626 |
5 | E | 21.59 | 2.626 |
6 | F | 8 | 1 |
7 | G | 24.32 | 3.192 |
8 | H | 27.65 | 3.062 |
9 | I | 93.5 | 13.768 |
10 | J | 56.76 | 7.1 |
The problem is that i would like to have the percentile of the visible rows of this Table based on the total displayed in the Net sales column. Any ideas? thanks a lot
Solved! Go to Solution.
Erm... spam filter marked my message as spam, trying it again, hope this works!
Create a calculated TABLE:
Table =
SUMMARIZE (
'data for power BI porcentile',
'data for power BI porcentile'[Project No.],
"Net Sales", SUM ( 'data for power BI porcentile'[Net Sales] )
)
Then add a calculated column to it:
Net Sales % =
DIVIDE (
'Table'[Net Sales],
CALCULATE (
SUM ( 'Table'[Net Sales] ),
ALL ( 'Table' )
),
0
)
Finally create the measure:
80th Percentile =
IF (
ISBLANK ( MAX( 'Project Nr'[Project No.] ) ),
BLANK(),
CALCULATE (
PERCENTILE.INC ( 'Table'[Net Sales %], 0.8 ),
ALL ( 'Table' )
)
)
Connect it in the relationship view to get the Product name and voila:
File here: .pbix
Hi @Vandergledison ,
Sorry that i missed explain NetSales1 =
Hi @Vandergledison ,
Please find the pbix attached.
This helps in filtering and ranking your products.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Hi @Vandergledison ,
Here you go. Hope this is what your requirement is.
Please find the pbix file attached.
You do not need the newly calculated table. There is a function in DAX for %ile calculation for expressions.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Dear @Vandergledison ,
The idea is not to confuse you. We are here to help you.
Your requirement was
1. Calculate the %ile based on Total Sales (or Sale%) for all projects.
2. Based on the %ile Calculated you wanted to Rank them i.e if the %ile is you wanted rank 5 (higher than 80p), 4 (higher than 60p), 3 (higher than 40p), 2 (higher than 20p) and 1 for the rest.
3. You also wanted the formula to Calculate the new %iles dynamically i.e if 50 projects are selected it should only Calculate the %iles of those 50 products and then Rank them based on the same criteria.
I hope my understanding is correct as your requirement has changed everytime we provided a Solution.
1. Confusion on PERCENTILE.EXC and PERCENTILEX.EXC
We were earlier calculating %iles based on a Summary Table having a row for Percentile.EXC. This same can be calculated using PercentilEX.EX where you can pass a measure instead of a Column. Please see what the formula does.
PERCENTILE.EXC
Returns the k-th (exclusive) percentile of values in a column.
Column | A column containing the values. | |
K | Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points. |
Table ITERATOR | Table over which the Expression will be evaluated. | |
Expression ROW CONTEXT | Expression to evaluate for each row of the table. | |
K | Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points. |
Hi @Vandergledison ,
Please find 2 files attached.
v4 is using related and as per your grouping.
v2 is creating clusters based on Total Sales.
Hope it solves the issue.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
The ALLEXCEPT Calculates the Total Sales grouping the Sales with project number.
Read AllEXCEPT, Remove all FILTER except on the column mentioned.
Since you are calculating percentiles for the sales with project no, this needs to be the measure over which %ile calculation will be done.
The Twenty% is used when you filter via project no. It will calculate the %iles of the selected rows only and not the entire Sales Column.
Regards,
Harsh Nathani
This is quite interesting and i spend the whole day changing the DAW and trying to learn what would be the variations by changing some fo the paramenters. The AllEXCEPT was difficult to understand but i think that i got it how it works.
My last step, is to create Clusters, or Groups to consolidate the project Nrs.
i thought that would be very easy, i added a new filter A (for the first 25 projects), B (next 25), and so on.
the problem now, is that due to that ALLEXCEPT, if i understood correctly, it will not consider previous filters, which makes the Rank on %ile not work on that table.
in the end, i will need to Select A, and have the (e.g) 25 projects which its respective %ranking as you provided.
i know that is more than abusing but any idea on how to work this around?
thanks a lot
Hi @Vandergledison ,
Please find 2 files attached.
v4 is using related and as per your grouping.
v2 is creating clusters based on Total Sales.
Hope it solves the issue.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Hi Harsh,
The V4 worked perfectly. Im just trying to understand the mechanics and one thing i couldnt understand. You had to chreate a Column [Clusters] that relates to the [MKST] from the 'Masterdata'. why doesnt work to simply grab the [MKST] column and add in the visualization if both table are connected via a relationship.
in the future, the 'Masterdata' will be a DIM table containing several categories to act as filters. i thought that i could simly create different Filters in the futre with the categories in 'Masterdata' filtering the 'Sales' table.
rgds
VG
Thank you so much.
Dear Harsh,
im sorry if somehow i misscomunicate. The final request didnt change and your way of thinking was spot on!
on your last file, i missed the EX on the percentilEX.exc and i thought that was the same, so thats why i couldnt see the difference and obviously the values in excel werent the same.
thanks a lot for your extra patience!!!
I learned a lot
VG
i totally undestood. thanks.
look, just one thing before i can move full throttle:
you wrote this before the "Calculate" part:
ISBLANK ( MAX( 'Project Nr'[Project No.] ) ),
BLANK(),
why is this necessary?
thanks
@Vandergledison Try to remove the IF statement and only have the CALCULATE statement and see what happens visually in your table. Then you will understand why I'm using the IF ISBLANK statement.
Thanks for accepting my solution.
1. i have already in my original data table the Net Sales for each product. if i simply add the column Net sales column on the side of each Product nr. i would have automaticly the Sum of this Net Sales column for each product. Why do i have to create a new one ("Net Sales") in the new table?
Creating a new table is required as Percentile requires a column for Calculation. The formula for Percentile requires a column and a value. PERCENTILE.EXC ( <Column>, <K> )
Net Sales is a Column but the Sum of Net Sales internally calculates the percentile separately for A, B, C , D as row context is of product name.
A has 10 rows and it calculates the percentile for these 10 rows and that is the reason you were getting 6.77 for A when you calculated Percentile Net Sales 0.8.
This is the reason we are creating a separate table with a column net sales and then calculating the percentile over net sales %.
Hope this makes sense now.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
ahhhh, now makes a lot of sense.. it still seems to me a workaround and i just need to understand the mechanics so i can run my own.
look, now im trying to create an IF function. i manged to do it, however i had to create a new calculated column.
when i tried to create this column inside of the measured table as you did, somehow doesnt work. when i start typing "rank net sales"=if(.... - I cant choose the new columsn of my new table,
i want simply to do a :
rank Net sales% = IF('New table'[Net Sales %]>=[80th Percentile],5,IF('New table'[Net Sales %]>=[60th Percentile],4,IF('New table'[Net Sales %]>=[40th Percentile],3,IF('New table'[Net Sales %]>=[20th Percentile],2,1))))
is it possible to do the same but not in a new calculated column but inside of the measure table you´ve created?
thanks
Hi @Vandergledison ,
Do you mean you want to show the percentile of the netsales?
If this is the case, all your products will have percentile equal to the net sales, as there is only one entry for each of the net sales of the product and the row context is of the product.
Incase you want a card to show you .8 percentile of the netsales the value comes out to be 86.64.
You can create a Summary Table:
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
In case it did not help, please provide additional information and mark me with @ so that I can investigate further
Hi Harsh, the sales i have in the table are the total Sales for each product. This values came from another table containing the sales per year. what i need now is a new measure that provides me the percentile 80% of each product but now based on the total net sales as you see bellow
Project No. | Product name | Total Net Sales |
1 | A | 59.18 |
2 | B | 52.33 |
3 | C | 103.2 |
4 | D | 21.82 |
5 | E | 21.59 |
6 | F | 8 |
7 | G | 24.32 |
8 | H | 27.65 |
9 | I | 93.5 |
10 | J | 56.76 |
The values for each product will be the same. What i want to do in the end is to Rank each product. I want to have in each row an if function that will score each product regarding its location within a certain percerntile. for example if product A has total sales higher than the percentile 80% it should get a 5, if its higher than 60% and lower than 80%, it should get a 4, and so on..
i hope its more clear.
thanks
basically i would like to do like in EXCEL.
in excell i applied the formula: =PERCENTILE.INC($D$2:$D$11,0.8)
in column E is showing the result. i just want the same in power BI via some measure.
A | B | C | D | E |
Project No. | Product name | Net Sales | % of total net sales | 80% Percentile from % of total net sales |
1 | A | 59.18 | 13% | 14% |
2 | B | 52.33 | 11% | 14% |
3 | C | 103.2 | 22% | 14% |
4 | D | 21.82 | 5% | 14% |
5 | E | 21.59 | 5% | 14% |
6 | F | 8 | 2% | 14% |
7 | G | 24.32 | 5% | 14% |
8 | H | 27.65 | 6% | 14% |
9 | I | 93.5 | 20% | 14% |
10 | J | 56.76 | 12% | 14% |
Hi @Vandergledison ,
Create a table.
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
In case it did not help, please provide additional information and mark me with @ so that I can investigate further
Hi,
thanks a lot, im still cracking my head to understand what you did (sorry, very new to Power BI).
somehow, when i tried to select the [Net Sales] from the table 'data for power BI porcentile' it doesnt work, it only shows the availible measures.
by just copy pasting what you have sent me, it doesnt find the [Net Sales1] i tried to change to [Net Sales] and also didnt work.
Hi @Vandergledison ,
Sorry that i missed explain NetSales1 =
Thanks a lot for your quick suport.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |