Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone,
I'm new to power BI, I have calculated result to get security name rank-wise, I have use RANKX Function ,code is like this
CALCULATE(
MAX( Sect[Security Name]),
FILTER(
VALUES ( ContributionCategory ),
ContributionCategory[SECTOR] = fourthPortfolio
),
FILTER(Sect,
Sect[Security Name] <> BLANK()
),
FILTER (
VALUES( Sect[Security Name] ),
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
)
)
here ,I want to get result which shows on first rank, but here I'm getting blank or nonText(empty) value on rank 1 and also in 2,3,4,5 rank.
and I have tried like this on 6 rank
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 6
then I'm getting proper result, but I want to ignore all the blank to nontext value in rank, and want six rank value on first rank ,
so how can i Ignore or filter all the blank or nontext value...
can anyone help me ?
Hi @kjshah5030,
Could you please share you sample data or pbix to me so that I can help with that.
Regards,
Frank
Hi @v-frfei-msft
Sure ,
Please find pbix file I have put in dropbox
https://www.dropbox.com/s/off4875xjsl7bez/Cyborg%20Equity%2018Q1%20_changeWithShum.pbix?dl=0
Here is the pbix file,In pbix file there is one matrix table,
Let me explain what I'm trying to do , I'm trying to get Top 1 sector Name Based on Highest End Weight(Benchmark) , means you can see in ex , Highest Weight is 23.17 which sector Name is Financials ,and I have got that Name using below Example
var sectorName = CALCULATE(
MAX(ContributionCategory[SECTOR]),
FILTER (
VALUES ( ContributionCategory[SECTOR] ),
RANKX ( ALL ( ContributionCategory[SECTOR] ), AllMeasure[Total Return Contribution],, DESC ) = 1
)
)
Now ,I want to get Top 1 SecurityName Based on Highest Total Return Contribution by filtering This sector ('Financial'), means I want 'Financial' sector's Top 1 Security Name Using Highest Total Return Contribution with Ignoring all the Blank and NonText ...
Let me explain Tables and Fields which we will be use...
FieldName TableName
1) SECTOR ContributionCategory
2) End Weight(Benchmark) AllMesure
3) Total Return Contribution AllMesure
4) Security Name Sect
and I have Tried with this example , But I'm Not getting as I expected
TestFields =
var sectorName = CALCULATE(
MAX(ContributionCategory[SECTOR]),
FILTER (
VALUES ( ContributionCategory[SECTOR] ),
RANKX ( ALL ( ContributionCategory[SECTOR] ), AllMeasure[Total Return Contribution],, DESC ) = 1
)
)
var data =
CALCULATE(
MAX(Sect[Security Name]),
FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName),
FILTER (
VALUES ( Sect[Security Name] ),
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
)
)
return data;
using this example I'm getting blank value on first Rank
and when I tried with below RANKX Functions in data variable
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 2
then I'm getting security name, so I want to Ignore or filter all the blank or NonText value From first rank ,and want 2 rank value on first ;
Thanks in advance
Hi @kjshah5030,
Based on my test, we can create the measure to meet your requirement.
Measure 3 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLSELECTED(ContributionCategory),[Total Return Contribution],,DESC,Dense))
TestFields111 = var sectorName = CALCULATE( MAX(ContributionCategory[SECTOR]), FILTER ( VALUES ( ContributionCategory[SECTOR] ), [Measure 3]=1 ) ) var data = CALCULATE( MAX(Sect[Security Name]), FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName), FILTER ( VALUES ( Sect[Security Name] ), [Measure 3]=1 ) ) return data
Here is the result for your reference.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/ory81yxn5538qi6/Cyborg%20Equity%2018Q1%20_changeWithShum1.pbix?dl=0
Regards,
Frank
Hi @v-frfei-msft
Thanks for Kind reply ,
But I'm not getting expected result ,
I want to filter all the 'Financial'(means first rank sector) sector security name (Not all security) and want that security name which AllMeasure[Total Return Contribution] is high
Means In this case
may be sicurity name will came "DBS Group Holding Ltd"
can you please see once more time
Thanks Again for helping me
Hi @kjshah5030,
Again I have a try to add the measures as below to meet your requirement.
Measure 3 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLSELECTED(ContributionCategory),[Total Return Contribution],,DESC,Dense))
Measure 4 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLNOBLANKROW(Sect[Security Name]),[Total Return Contribution],,DESC,Dense))
TestFields111 = var sectorName = CALCULATE( MAX(ContributionCategory[SECTOR]), FILTER ( VALUES ( ContributionCategory[SECTOR] ), [Measure 3]=1 ) ) var data = CALCULATE( MAX(Sect[Security Name]), FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName), FILTER ( VALUES ( Sect[Security Name] ), [Measure 4]=1 ) ) return data
Here is the result I got, Seemed the result should be Constellation Software Inc.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/ory81yxn5538qi6/Cyborg%20Equity%2018Q1%20_changeWithShum1.pbix?dl=0
Regards,
Frank
Hi @v-frfei-msft
I'm very sorry for late reply , I was busy in something else , so sorry for that
and about above result sorry but i'm not getting expected result
let see in ex... , there is a sector dropdown and account dropdown , select value like "Mawer International equity fund" from accout dropdown and from sector "Financials" , then u can see in your result "DBS Group Holdings Ltd" I want this name on first rank
but you have to assume that we dose not have sector dropdown .
Thanks,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |