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

Accepted Solutions
Highlighted
Super User V
Super User V

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

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

Highlighted
Super User V
Super User V

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

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

Highlighted
Super User V
Super User V

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

@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
Highlighted
Super User IV
Super User IV

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

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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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

prio list 

 

 

 

Highlighted
Super User V
Super User V

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

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

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

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

Hi @Vandergledison ,

 

Happy to help 🙂

 

Take Care.

 

Regards,

Harsh Nathani

 

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

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

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

Highlighted
Helper III
Helper III

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

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

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

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors