cancel
Showing results for
Did you mean:
Highlighted
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.

VG

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User V

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

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)

Regards,
Harsh Nathani

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

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

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!!
Highlighted
Super User V

## Re: is there a way to create a table extracting results from a 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!!

15 REPLIES 15
Highlighted
Super User IV

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

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

Thanks,

Pragati

If this helps, Appreciate a KUDOS!

Proud to be a Super User!

Highlighted
Helper III

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

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

Highlighted
Super User V

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

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)

Regards,
Harsh Nathani

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

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

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

Highlighted
Super User V

Happy to help 🙂

Take Care.

Regards,

Harsh Nathani

Highlighted
Helper III

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

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

Highlighted
Super User V

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

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!!
Highlighted
Helper III

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

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!
Highlighted
Super User V

## Re: is there a way to create a table extracting results from a 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!!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021