topic Re: is there a way to create a table extracting results from a measure? in DAX Commands and Tips

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

Vandergledison — Mon, 11 May 2020 12:46:13 GMT

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

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

Pragati11 — Mon, 11 May 2020 15:43:28 GMT

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

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

Vandergledison — Mon, 11 May 2020 16:52:29 GMT

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

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

Anonymous — Mon, 11 May 2020 18:20:00 GMT

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)

https://drive.google.com/file/d/1IfKG_qUyMj9Jw_0dHh7FGkHMfIy8uytL/view?usp=sharing

Regards,

Harsh Nathani

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

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

Vandergledison — Mon, 11 May 2020 18:42:41 GMT

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!!

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

Anonymous — Mon, 11 May 2020 18:45:21 GMT

Hi Vandergledison ,

Happy to help 🙂

Take Care.

Regards,

Harsh Nathani

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

Vandergledison — Tue, 12 May 2020 15:35:49 GMT

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

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

Anonymous — Wed, 13 May 2020 05:04:37 GMT

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.

Regards,

Harsh Nathani

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

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

Vandergledison — Wed, 13 May 2020 12:06:29 GMT

Anonymous ,

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!

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

Anonymous — Wed, 13 May 2020 13:53:58 GMT

Vandergledison ,

Please use the below measure.

Selected Cluster = CONCATENATEX(

SUMMARIZE(

Sales,

Sales[Cluster],

"Filter Applied",FILTERS(Sales[Cluster])), [Filter Applied] , ",")

Regards,

Harsh Nathani

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

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

Vandergledison — Wed, 13 May 2020 16:13:19 GMT

Maaan, You are a Ninja. thanks a lot

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

Vandergledison — Fri, 15 May 2020 16:03:08 GMT

Dear Anonymous ,

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

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

Anonymous — Fri, 15 May 2020 16:20:02 GMT

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

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

Vandergledison — Fri, 15 May 2020 17:35:48 GMT

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

PrioList100b

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

Anonymous — Sat, 16 May 2020 13:06:37 GMT

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-display-visuals/m-p/950687

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

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!!

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

Vandergledison — Mon, 18 May 2020 08:19:11 GMT

Thanks a lot Anonymous .