Forum Discussion

krzysztof_golda's avatar
krzysztof_golda
Frequent Visitor
4 years ago

Percentile.INC for categories and dates dynamically

Hello,

 

This is my first time I am asking for help in this community, so please accept my apology if I forgot to include anything crucial to solve the problem, of course I will do it later if necessary.

I looked here for my problem but I did not find any solution which might fulfill the needs.

Recently I have been asked to create a report which will show percentile.inc on the line and clustered column chart. The problem is that my data contains some values based on which the measure is created (Measure = DIVIDE(CALCULATE(SUM('table2'[Cases])) * 200000,CALCULATE(SUM('table2'[Hours]))), and moreover each plant exists in the database per date (one row for one date - monthly basis). The request is to being able to filter dataframe (for instance 6 months only or just 1) and check what is the percentile per plant complexity or total and show it on the chart as a line (finally it will be 4 measures showing four different percentiles) and X-Axis by plant. I have two tables, one with plant masterdata and one with the data per plant. PERCENTILE.INC doesn't accept measure as a first argument which have to be column. I cannot store the indicated measure as a column because it needs to be calculated as mentioned (cannot be the average).

Usually the solution that I found on net is to create another table with summarized data but I do not want to do that as I want to be able to filter the dates.

This is table1 which contains plant masterdata.

RegionPlantPlant Complexity
CCM
EGM
IHL
NKH
IPL
ISH

This is table2 which contains data per plant:

DateHoursCasesPlant
01.01.20222319250C
01.01.2022875950G
01.01.20224000H
01.01.20221265000K
01.01.2022525120P
01.01.2022915321S
01.02.20221830740C
01.02.20221056330G
01.02.20224000

H

01.02.20221260000K
01.02.2022519360P
01.02.2022827701S
01.03.20221339450C
01.03.20221163120G
01.03.20227200H
01.03.20221260001K
01.03.2022588600P
01.03.2022899610S

Many thanks for any help in advance, for anything more please let me know and I will provide.

 

thank you!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi krzysztof_golda ,

     

    According to your statement, I think your requirement is not very clear. I think two tables should be related by [Plant] column. 

    Measure =
DIVIDE(CALCULATE(SUM('table2'[Cases])) * 200000,CALCULATE(SUM('table2'[Hours])))

    This measure should work to calculate the percentage. Please show me a screenshot with the result you want. This will make me easier to find the solution.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • krzysztof_golda's avatar
    krzysztof_golda
    Frequent Visitor
    4 years ago

    Hi Anonymous ,

    Many thanks for the reply, appreciate it,

    So let me explain, the request is to create 4 measures which will show accordingly 25th, 50th, 75th and 90th percentile. Each plant have its complexity, and also we have a timeline filter, so the aim is to filter by time and the complexity. For instance: when I choose last 6 months and no complexity then measure should calculate 25th percentile for whole organisation for last 6 months, but when I choose last 6 months AND the complexity low, I should see what is the 25th percentile for those plants which are low. The percentile measures will be put into the column chart and line chart (percentiles as lines, the value should be constant across the company and related to filters of complexity and timeline). Important thing is that if only one plant is chosen, the measure should calculate percentile only to the level of complexity, not plant.

    Currently I am using the percentile from the analysis tab of the visual, but when plant is chosen, it shows the percentile for this certain plant and not for the complexity.

    This should look like below, where in the X-Axis we have plants and the Y-Axis is the mentioned calculation:

    Measure =
DIVIDE(CALCULATE(SUM('table2'[Cases])) * 200000,CALCULATE(SUM('table2'[Hours])))

    If anything more is necessary let me know, 

    Greetings!