topic Re: Dax to Determine Filtered Percentages in DAX Commands and Tips

Dax to Determine Filtered Percentages

Thackeb — Thu, 05 Aug 2021 20:04:55 GMT

Please note that I can only build DAX, I do not have access to Power Query or Caluclated Columns.

I have a field for Account Managers and a field for Attainment % Trend. Example..........

Ronald Reagan 85.9%

Jimmy Carter 101.8%

I have 3 brackets..............

-Less than 87.5%

-87.5 to Less than 100%

-100% or Greater

First I would like to count, how many fall into each bracket.

Second I would then break this down by what % of all fall into each bracket. (which I think can be easily done once I have the breakdown.)

I've tried distinctcount, filter.....everything I could imagine. I'm not a new user, but can't figure out how to make this work.

Any help is greatly appreciated unless you tell me to build a new table or add a calculated columns. LOL!

Re: Dax to Determine Filtered Percentages

amitchandak — Fri, 06 Aug 2021 05:58:41 GMT

Thackeb , Based on what I got you need bucketing or binning. You need an Independent table of % range and you also need new measures grouped based on names

Refer if these can help

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Re: Dax to Determine Filtered Percentages

Thackeb — Fri, 06 Aug 2021 13:09:10 GMT

Thank you...after thinking about it last night, I thought SWITCH might be an option.

Re: Dax to Determine Filtered Percentages

Thackeb — Fri, 06 Aug 2021 16:52:55 GMT

Additioanl question so sorry......

I added the switch Dax. If it's in a table with a the account manager name, it notes if they are Good, Neutral or Needs Improvement. If I don't have the name, it shows Neutral.

Attainment Metric =

SWITCH(

TRUE(),

[*Attain % Trend] < .875, "Needs Improvement",

[*Attain % Trend] >=.875 && [*Attain % Trend] < 1, "Neutral",

[*Attain % Trend] >= 1, "Good")

Is there a way to write this so it counts how many are Good, Netural, Needs Improvement?

Currently Data looks like this..........

Account Mgr Name Attainment Metric Attainment

John Wayne Good 101%

Ronald Reagan Neutral 88.9%

Nancy Reagan Neutral 99.4%

Jimmy Carter Good 102.8%

George Washington Needs Improvement 79.5%

Again...DAX is my only option as I cannot edit tables or calculated columns.

Thank you!