Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vandergledison
Helper III
Helper III

is there a way to create a table extracting results from a measure?

Dear all,

i have a RankX measure that ranks based on another measure "rank total".

the rank total is also a measure that contains several other transformations.

In order to simplify the Rankx or Top N measure, im wondering if its possible to create a column or measure that extracts the numbers of the measure "rank total" and transform it in simple numbers. Then i will be able to aply the RAnkx or top N in this measure or Column.

thanks in advance

VG

3 ACCEPTED SOLUTIONS

Hi @Vandergledison ,

 

Please use following measures

 

final rank untie = [Total Sales by Project No]*10000 + AVERAGE(Sales[SysProjectID])
 
Prio list = RANKX(ALLSELECTED(Sales[Project No.],Sales[Cluster]),[final rank untie],,DESC)
 
 
1.jpg2.JPG3.JPG
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Hi @Vandergledison ,

 

From the last .pbix attached.

 

Create a Calculated Column

 

FilterSales = [Total Sales by Project No]
 
You can now drag this column as a filter and filter the visual by Sales.
 
1.jpg
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

@Vandergledison ,

 

 

Please use the below measure.

 

Selected Cluster = CONCATENATEX(
SUMMARIZE(
Sales,
Sales[Cluster],
"Filter Applied",FILTERS(Sales[Cluster])), [Filter Applied] , ",")
 
 
1.jpg
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 

View solution in original post

15 REPLIES 15
Pragati11
Super User
Super User

Hi @Vandergledison ,

 

Can you please provide additional information like your calculations, sample data, etc. ? 

It's hard to answer your question this way.

 

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Hi Pragati, first of all thanks a lot for helping,

Basically, i have years of sales by project and i need to rank them. I have aplied percentilex in order to be able to have a sales score. top 20% =5, 20-40 = 4 and so on.

in the end i have a Sales Score per project. so far so good.

Later, i need to build also a score per CM1 and net present value.

in the end i will have a mesure that will be (Net sales score + CM1 score + NPV score)

in this example, i have only the Net sales to facilitate.

now, i need to rank this net sales score. As a lot of them have the same score, the RankX function will have ties.

to break this ties, i multiplied the Sales score by 1000 and sum the unique project id (just to have a unique number)

you will see that the numbers are "jumping" in the rank. (1,2,3,4,16,17,etc).

This prio list (rankx) needs to show me the rank of projects every time i select a Cluster (shown in the filter).

if i select 1 cluster its kind of working, howwer, if i select all of them, or 2 or 3, the Rankx (prio ) doesnt work anymore.

i have some "allselect" and "allexcept" functions that are there in order to make the percentilex function work.

at the end i just need to have a prio list from 1 til XXX dinamically changing when choosing Cluster A or B but also A+B and all of them as well.

i hope its a bit more clear.

file bellow, thanks a lot

prio list 

 

 

 

Hi @Vandergledison ,

 

Please use following measures

 

final rank untie = [Total Sales by Project No]*10000 + AVERAGE(Sales[SysProjectID])
 
Prio list = RANKX(ALLSELECTED(Sales[Project No.],Sales[Cluster]),[final rank untie],,DESC)
 
 
1.jpg2.JPG3.JPG
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Hi Harsh, 

you again saving me! 

look. as the file ended up with a lot of different measures with specific "allselected" and "allexcept" functions, i found where i messed up. from your file, i added an extra column in the visual which was a Project name. I added it extra in the other measures filters, but somehow i forgot in one of them. now, its all working thanks to you.

super many thanks!!

Hi @Vandergledison ,

 

Happy to help 🙂

 

Take Care.

 

Regards,

Harsh Nathani

 

Harsh,

do you think that its possible to add a Slider Filter containing the Net sales ? The idea is to Filter the Priority table by a certain Total of Net sales, lets say: if i filter the slide to $50000 , the table should show me only the top projects where their sum fits to $50000.

To complex?

thanks

VG

Hi @Vandergledison ,

 

From the last .pbix attached.

 

Create a Calculated Column

 

FilterSales = [Total Sales by Project No]
 
You can now drag this column as a filter and filter the visual by Sales.
 
1.jpg
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

@harshnathani ,

I was trying to display the selected Cluster(s) in a separated Card.

The Card will be displayed on the top of other table showing the Budget and the Sales for the selected clusters.

i was trying the DAX bellow but it didnt work:

Clustersfiltered =
IF (
ISFILTERED (Sales[Cluster]),
CONCATENATEX ( FILTERS ( Sales[Cluster] ), Sales[Cluster], ", " ),
"ALL"
)
 
is there any way to display the selected clusters in a Card?
thanks again!

@Vandergledison ,

 

 

Please use the below measure.

 

Selected Cluster = CONCATENATEX(
SUMMARIZE(
Sales,
Sales[Cluster],
"Filter Applied",FILTERS(Sales[Cluster])), [Filter Applied] , ",")
 
 
1.jpg
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 

Dear @harshnathani ,

my total ranking is a sum of several measures, [Net sales rank]+[CM1 Rank]+[Customer Rank], etc..

i can create a filter where i can select each of this measures and have a different Total ranking.

my problem is with the combination of 2,3 or all of the selected ranks.

 

Total Ranking = SWITCH(TRUE(),
                MAX('Criteria for Score'[Score criteria])="Net Sales", [Net Sales Rank],
                MAX('Criteria for Score'[Score criteria])="CM1", CM1[CM1 Rank], etc..
 
Is there an elegant or just better way of having this Total ranking measure reflecting the sum of the Selected
only Elements in a slicer. in the End, i would like to have a dynamic Total rank, which will only take into account
the elements selected in the slicer.
thanks in advance
VG

Hi @Vandergledison ,

 

How are you doing.

 

Apologies, I did not understand a bit of what you need. Can you elaborate on this requirement like you did in the first post here.

 

Also, if you can share your .pbix file. Will be helpful to understand.

 

Regards,

Harsh Nathani

Dear Harsh, no need to apologize

its quite difficult to explain clearly what i need 😛

 

So in the file you will see that i have 2 main percentile measures. One is the Net Sales and the other is NPV. I will have more in the future.

Now the totalRank is the Sum of NetSales rank adn NPV rank. This Sum will reflect what the RankX will order.

i added a filter (it doenst need to be a filter) just to show what i need:

basically in the Box i want to show the final Rank dynamically based on the factors that contribuite to the total RAnk. 

If i select only Net Sales in the box, it should only show the Rank based on Net sales, If i click Net sales and NPV, it should show the Sum of both of them..and so on.

i hope its clear when you see the file.

thanks a lot

VG

PrioList100b 

 

@Vandergledison ,

Thanks, I understand what you require now. 

 

Please see below post,

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Slicer-mulitple-value-store-and-then-use-to-d...

 

I am really not sure how this can be done as the requirement is to add measures based on Selected Slicer Value. If multiple values are selected, then add multiple measures.

 

 

There can be a different method though, where your slicer value contains values like for Scoring Criteria

 

1.jpg

 

This may not be feasible incase you  have a lot of parameters to calculate your final rank.

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

 

Thanks a lot @harshnathani .

 
               

Maaan, You are a Ninja. thanks a lot

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors