Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI Guru's,
I am not a programmer & new to Pwer BI.
I seek your help in a custom solution to ranking my data under "Matrix" visualization which I am building for Top 50/100 or 300 customers from my table, such that wierd names like "Multiple Customers" or some bankrupt customers records are kept out of the displayed Matrix BUT, they continue to contribute to total value.
The 2nd row of the table in blue color is my table header.
1) First, I should be able to assign a custom rank number to the specific values in the field "Customer" (say "Multiple Customer" or any bankrupt customer.
2) then, the rest records are ranked based on the field "Financial Year" and sum of field "Values USD". No problem if the custom rank numer I chose in point 1 above, is also assinged to other records by the PBI formula.
The result I expect is in the field "Expected Resut".
Please help with this unusual help sought 🙂
Thanks,
Chandan.
Text field | Text field | Text field | Text field | numeric field | ||
Customer | Product | Month | Financial Year | Values USD | Expected Result | Condition |
Microsoft | Mobile | July' 2015 | 2015-16 | 4,000 | 1 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
Mobile | Aug' 2015 | 2015-16 | 3,000 | 2 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Apple | Mobile | July' 2015 | 2015-16 | 2,000 | 3 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
General Motors | Mobile | July' 2015 | 2015-16 | 1,000 | 4 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
Multiple Customers | Mobile | Aug' 2015 | 2015-16 | 5,000 | 301 | <- conditioned to assign Rank no 301 or any such custom Rank number defined by me |
Cargill | Mobile | July' 2015 | 2015-16 | 100 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Walmart | Mobile | July' 2015 | 2015-16 | 50 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Microsoft | Mobile | June' 2016 | 2016-17 | 4,130 | 2 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
Mobile | May' 2016 | 2016-17 | 6,098 | 1 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Apple | Mobile | Mar' 2017 | 2016-17 | 2,065 | 3 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
General Motors | Mobile | April' 2016 | 2016-17 | 1,033 | 4 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
Multiple Customers | Mobile | September' 2017 | 2016-17 | 5,163 | 301 | <- conditioned to assign Rank no 301 or any such custom Rank number defined by me |
Cargill | Mobile | October' 2016 | 2016-17 | 103 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Walmart | Mobile | September' 2016 | 2016-17 | 52 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Microsoft | Mobile | 2017-18 | 4,264 | 2 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Mobile | 2017-18 | 6,296 | 1 | <- as per formula based on field "Financial Year" & sum of "Values USD" | ||
Apple | Mobile | 2017-18 | 2,132 | 3 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
General Motors | Mobile | 2017-18 | 1,066 | 4 | <- as per formula based on field "Financial Year" & sum of "Values USD" | |
Multiple Customers | Mobile | 2017-18 | 5,330 | 301 | <- conditioned to assign Rank no 301 or any such custom Rank number defined by me | |
Cargill | Mobile | 2017-18 | 107 | <- as per formula based on field "Financial Year" & sum of "Values USD" | ||
Walmart | Mobile | 2017-18 | 53 | <- as per formula based on field "Financial Year" & sum of "Values USD" |
Solved! Go to Solution.
Hi @Anonymous
If you are looking for a MEASURE, you can use this formula
RANK_Measure = IF ( SELECTEDVALUE ( TableName[Customer] ) = "Multiple Customers", 301, RANKX ( FILTER ( ALL ( TableName ), TableName[Financial Year] = SELECTEDVALUE ( TableName[Financial Year] ) && TableName[Customer] <> "Multiple Customers" ), CALCULATE ( SELECTEDVALUE ( TableName[Values USD] ) ) ) )
HI @Anonymous
Please see the attached file here
Hope it helps
I just added a calculated column as follows
RANK = IF ( TableName[Customer] = "Multiple Customers", 301, RANKX ( FILTER ( ALL ( TableName ), TableName[Financial Year] = EARLIER ( TableName[Financial Year] ) && TableName[Customer] <> "Multiple Customers" ), TableName[Values USD] ) )
Hi @Anonymous
If you are looking for a MEASURE, you can use this formula
RANK_Measure = IF ( SELECTEDVALUE ( TableName[Customer] ) = "Multiple Customers", 301, RANKX ( FILTER ( ALL ( TableName ), TableName[Financial Year] = SELECTEDVALUE ( TableName[Financial Year] ) && TableName[Customer] <> "Multiple Customers" ), CALCULATE ( SELECTEDVALUE ( TableName[Values USD] ) ) ) )
Hi @Zubair_Muhammad, Thanks for this help. I will check it over this weekend, since PBI is new to me & hence I am slow in using it, I will take leisure time to learn & absorb. Will reply you on Sunday. Thanks Again !!
Hi @Anonymous,
In your expected result, there are some blank row, why they are blank, you only want to display the rank number which is less than 4 in your sample table?
Best Regards,
Angelia
Hi @v-huizhn-msft , Yes, Rank is required for those blank cells . I kept it blank in the "expected result" field but the adjacent field named "condition" states " as per formula based on field "Financial Year" & sum of "Values USD" ".
Hi @Anonymous,
Ok, got it. Please try the solution @Zubair_Muhammad posted, please mark the right reply as answer if your issue has been resolved, and respond to us if you still have other problems.
Best Regards,
Angelia
Hi @v-huizhn-msft, yes I got to check that offered solution given by @Zubair_MuhammadZubair Mohammad. I thought if in case you want to contribute from your side in a different way 🙂
Hi @Anonymous,
Have you tried the solution @Zubair_Muhammad posted? Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.
Best Regards,
Angelia
Hi @v-huizhn-msft, the solution worked by Mr. Zubair for summarised recrods in a table. I am now looking for a cmore lengthy data with multiple values in the same financial year.
I will post the PBX file & tag you as well in the question.
Thanks,
Chandan