Calculating Percentile Score for various categories for different companies

DAX DAX Calculations
1

Hi all,
Thank you all for all the help provided in the forum.

I have different activities for different companies and their scores for each activity. I am trying to calculate the percentile for the activity categories for those companies except one. I have tried many DAX formulas and searched everywhere but not quite there yet.

My Table looks like this,
Company ID
Activity Date
Activity Category
Activity Score

What I am trying to get to is to find the 50th percentile for each activity category in a line graph and have two slicers one for the activity category and one for the companies and go from there.

The one company ID I would like to exclude from the calculation is " 2017202"

I am using the same Measure below in my actual dataset and it’s working but not giving me the right values I am trying to get.
When I tried to add it to my sample data as a reference, it didn’t work.

50th Percentile =

VAR

Temp_Table = SUMMARIZE(Table,‘Table’[Activity Date],‘Table’[Activity Category],‘Table’[Activity Score],“Column_Percentile”,SUMX(‘Table’,‘Table’[Activity Score]))

return

PERCENTILEX.INC(Temp_Table,[Column_Percentile], 0.5) *100

Thanks again for all the help.

Best Regards,

3

Hi @iasma, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>. image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum . Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

Hi @iasma, we’ve noticed that no response has been received from you since the 19th of March. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Firstly, let’s revise your measure to exclude the specific company from the calculation. We can do this by filtering out rows where the Company ID is “2017202” using the FILTER function. Here’s the updated measure:
50th Percentile =
VAR Temp_Table =
SUMMARIZE (
FILTER ( ‘Table’, ‘Table’[Company ID] <> “2017202” ),
‘Table’[Activity Date],
‘Table’[Activity Category],
‘Table’[Activity Score],
“Column_Percentile”, SUMX ( ‘Table’, ‘Table’[Activity Score] )
)
RETURN
PERCENTILEX.INC ( Temp_Table, [Column_Percentile], 0.5 ) * 100

In this measure, I’ve wrapped the SUMMARIZE function with a FILTER function to exclude rows where the Company ID is “2017202”. This ensures that only data from other companies is considered in the calculation.

Make sure to replace 'Table' with the actual name of your table. Also, ensure that the column names ('Activity Date', 'Activity Category', 'Activity Score', 'Company ID') match the columns in your dataset.