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
Jkaelin
Resolver I
Resolver I

DAX Measure Help! (Multiple TopN criteria?)

Good morning,

 

This measure is critical to our model, but I'm afraid it's too complex for me.  

 

Goal:  I want the "Average" of the Return Column, for the Top 2 of Factors {1,2, & 3} within the Top Half of Valuation field. 

In total, 6 records will be averaged.  A sample table is below, to the right of the sample table is the results displayed manually.  

 

TopN of Factors.PNG

My attempts have been unsuccessful so far.  I've obtained the average return for the top half of the valuation field via this measure:  Measure 1:=CALCULATE(average(Table1[Return]),Filter(Table1,[Valuation]<=PERCENTILE.INC([Valuation],0.5)))

 

I've obtained the average return for the Top 2 of Factor 1, within the Top Half of Valuation field via this measure:

=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]),[Measure1]).

 

When attempting to calculate the results for fields Factor 2 & 3, This method DID NOT work:

=AVERAGEX(TOPN(2,Table1,Table1[Factor 1]) && TOPN(2,Table1,Table1[Factor 2]),[Measure1]).

 

Any advice or tips on how to resolve this dilemma?  I need to rank multiple fields, within a field, and average them.

 

Thank you & kindly,

James

1 ACCEPTED SOLUTION

Here is a full solution, you could consolidate this to a single table/measure:

 

 

Table 1 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 1])

Table 2 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

Table 3 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 3])

Table 4 = UNION('Table 1', 'Table 2', 'Table 3')

Measure = AVERAGE('Table 4'[Return])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User
Super User

Wondering if you could break the problem down in a table with distinct primary ID's. Essentially, create a column/measure for each of the items you have displayed. You should be able to use RANKX for most of them. Could you post your sample data in a form that is easy to copy and paste into an Enter Data query? That would save a huge amount of time.

 

If you did it this way, you could possibly use filters to get what you wanted.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks for the quick response.  Not sure how to post sample data in a form that is designed for Enter Data query?  I would love to know & I will do it.  Below is a simple copy & paste from Excel.  I have considered RankX, but wasn't sure how to execute that for multiple fields.  

 

PrimaryIDReturnValuationFactor 1Factor 2Factor 3
2-79%0.0183.28.287.3
13-4%0.0472.863.648.1
4-38%0.0725.740.236.3
28-30%0.0755.46.037.8
16-72%0.0792.946.720.2
7-27%0.085.06.478.5
17-93%0.0885.038.650.0
25-3%0.1611.298.945.0
9-100%0.1830.281.064.5
23-16%0.2078.386.628.7
27-78%0.2196.960.913.2
14-69%0.249.348.547.5
24-51%0.2596.760.859.7
10-52%0.3251.178.979.9
21-40%0.3572.682.399.7
22-25%0.3659.592.313.3
12-9%0.5058.044.063.7
26-55%0.5036.918.752.8
29-22%0.5254.140.580.1
18-85%0.5755.051.770.9
30-42%0.6110.489.545.0
19-44%0.6316.147.013.3
15-1%0.6317.781.774.2
8-36%0.7057.689.828.8
11-96%0.8164.631.067.4
20-74%0.8518.672.028.6
3-78%0.8567.648.960.3
1-33%0.870.392.398.1
5-98%0.9319.522.031.0
6-92%0.9522.259.154.2

That method of pasting data is perfect!

 

Based on @TomMartens indicated, build your tables like this:

 

Table = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler @TomMartens

 

Maybe I'm mistaken, but this formula is returning a Semantic #Error, "The measure refers to Multiple Columns".  I think the [Factor 2] is causing it to error, but the logic seems to work.  What am I missing??

 

Table = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

 

You need to create it as a table, not a measure.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

Thanks for the tips.  I may just be in over my head here because I'm not sure what you mean by create it as a table vs. a measure.  I only have done calculated columns & measures.  Sorry for my ignorance.  

@Greg_Deckler@TomMartens

 

I believe I got the measure to get me the correct value.  I think it works now!  Thank you guys so much for your help.  

 

DAX Test Measure :=
CALCULATE (
    AVERAGEX (
        UNION (
            TOPN ( 2, Table1, Table1[Factor 1] ),
            TOPN ( 2, Table1, Table1[Factor 2] ),
            TOPN ( 2, Table1, Table1[Factor 3] )
        ),
        [Return]
    ),
    FILTER ( Table1, [Valuation] <= PERCENTILE.INC ( [Valuation], 0.5 ) )
)

 

Yep, that should definitely work as a single measure.

 

For future reference you can create a calculated table by going to the Modeling tab and clicking "New Table". The difference is that a table expects to return 1 or more rows versus a measure which expects a single value to be generated.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Here is a full solution, you could consolidate this to a single table/measure:

 

 

Table 1 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 1])

Table 2 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 2])

Table 3 = TOPN(2,FILTER(Factors,Factors[Valuation]<=PERCENTILE.INC(Factors[Valuation],.5)),[Factor 3])

Table 4 = UNION('Table 1', 'Table 2', 'Table 3')

Measure = AVERAGE('Table 4'[Return])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Wondering if this could be an idea
AVERAGEX(
UNION(

  TOPN(2,...,MEASURE1),
  TOPN(2,...,MEASURE2),
  TOPN(2,...,MEASURE3)
),
[RETURN]
)

To create sample data, maybe you export a table visual that contains the data, and upload the csv to OneDrive or DropBox  and share the link (please make sure that no unwanted aggregations have an effect of the data you want to export)



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

Thanks for your help!  The measure you provided gives me the return for the 3 factors, but did not account for the factors within the Top Half of the Valuation field.  Is it possible to include that within the measure?  The measure you did provide is very helpful.

 

This is the file on Dropbox (I think I did it correctly):  https://www.dropbox.com/s/ycotbvtcpipg7rk/DAX%20Oppty%20Measure.xlsx?dl=0

Hey,

 

the basic idea is to union 3 tables,

as far as i understod, you already have found the proper value for factor 1.

maybe you have to use 3 filter statements, that each return a table, even if this table just contains one column.

 

Maybe you find a way to share some data 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hmm, 

 

wondering what has to happen when

  • one PrimaryID satisfies more than one rule, meaning the same record is found in in the top 2 for factor 1 and factor 2
  • the 3 value is identical to the 2nd or even the Top 3 values are identical


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens  If a PrimaryID satisfies more than one rule, the total records will be reduced.  So instead of 6 total records, their would be 5 (b/c 1 record met 2 criteria).  Ultimately, this measure is to backtest some stock portfolios of 30 stocks.  So the portfolio would have maybe 25-30 holdings depending on stocks that have overlapping criteria met.  Thank you for your help!

Hey,

 

the basic idea is to union 3 tables,

as far as i understod, you already have found the proper value for factor 1.

maybe you have to use 3 filter statements, that each return a table, even if this table just contains one column.

 

Maybe you find a way to share some data

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.