Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I'm trying to get the top 20 percent of some clients. I'd like to create a filter which looks at the client data, if they are on the top 20 percent, write the value, if not, leave it blank. So that then I can filter in a table, summing the rows that have data (leaving the blank values outside the calculation). All these values summed (displayed in a row), are compared against some dates (which are in columns, the dates I mean)
Problem is that I cannot execute an if statement row by row. I'm comparing 2 values from the same row as an assertion, and leaving it blank if its lower than the other value, otherwise copy the value from one of the values I'm comparing in the statement.
I'm also having issues with the last part, for some reason power bi isn't suggesting the column I want to use for copying the value.
Thanks!
Solved! Go to Solution.
Yes.. Two options.
1. Create a conditional column or custom column in Query editor(Transform data )
or
2. Create a calculated column and write DAX
If it helps, mark it as a solution
Kudos are nice too
If you want to achieve this in power query, you can create a custom column in query editor, you can refer to doc:
https://docs.microsoft.com/en-us/power-bi/desktop-add-custom-column
In you want to add a new column in table using dax, please create a calculate column using dax function IF():
https://docs.microsoft.com/en-us/dax/if-function-dax
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you want to achieve this in power query, you can create a custom column in query editor, you can refer to doc:
https://docs.microsoft.com/en-us/power-bi/desktop-add-custom-column
In you want to add a new column in table using dax, please create a calculate column using dax function IF():
https://docs.microsoft.com/en-us/dax/if-function-dax
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @makcim392 ,
check this out.
SUMX
Returns the sum of an expression evaluated for each row in a table.
https://docs.microsoft.com/en-us/dax/sumx-function-dax
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Refer
https://www.burningsuit.co.uk/blog/2019/05/find-top-or-bottom-n-percent-dax/
https://community.powerbi.com/t5/Desktop/Top-N-percent-filter/td-p/682552
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
Yes.. Two options.
1. Create a conditional column or custom column in Query editor(Transform data )
or
2. Create a calculated column and write DAX
If it helps, mark it as a solution
Kudos are nice too
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |