Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
The report is to visualize the Cases by Top 3 Products (%) for the Firm, and for the Sector (all firms in the same sector).
Steps:
1. RankProductSector = if([SECTOR (Top 3 Product%)]<>0, (Rankx( ALL('Cases'[Product]), '_Key Measurements'[SECTOR (Top 3 Product%)], ,DESC)), 4) 2. SECTOR (Top 3 Product%) = DIVIDE ([Sector (Closed)_RevProd],[SECTOR_AllProd] ) 3. Sector (Closed)_RevProd = VAR SECTOR= CALCULATETABLE( VALUES('Cases'[Sector Name (merge)]), ALL(Dates), ALL(Mapping_Sector), aLL('Cases'[Product]) ) RETURN Calculate ([FIRM (Closed)], All('Cases'[Firm Name]), SECTOR) 4. SECTOR_AllProd = Var Sector= Calculatetable (Values('Cases'[Sector Name (merge)]), All(Dates) ) Return Calculate ([FIRM (Closed)], All('Cases'[Firm Name],'Cases'[Product]), sector) 5. FIRM (Closed) = Calculate( DISTINCTCOUNT('Cases'[Case Number]), filter ('Cases', 'Cases'[Date Case Completed]<>blank() ))
6.
TopProduct=min([RankProductFirm],[RankProductSector])
4. Prepare Matrix Table,
Filter: Firm Name=" ABC Company" and "TopProduct<=3"
Question: The RankProductSector for Fiscal Year 2015 and 2016 should rank 1,2,3 but "1" is missing. Same formula works well for Fiscal Year 2017 and 2018 they have Top ranking 1,2,3.
I am wondering if the Dax for "RankProductSector" is correct? Any help is appreciated. Thank you.
Hi @Anonymous
Based on my understanding,
1. RankProductSector rank SECTOR of product in each firm and in each Sectors?
2. SECTOR (Top 3 Product%)
3. Sector (Closed)_RevProd calcuate for each product in each firm in each Sector?
4. SECTOR_AllProd calcuate for all products in each Firm and in each sector?
5. FIRM (Closed)
for example:
sector | firm | product | FIRM (Closed) | Sector (Closed)_RevProd | SECTOR_AllProd | SECTOR (Top 3 Product%) | RankProductSector |
1 | a | 12 | 2 | 2 | 10 | "2/10" | 3 |
1 | a | 13 | 3 | 3 | 10 | "3/10" | 2 |
1 | a | 14 | 4 | 4 | 10 | "4/10" | 1 |
1 | a | 15 | 1 | 1 | 10 | "1/10" | 4 |
1 | b | 12 | 2 | 2 | 9 | "2/9" | 3 |
1 | b | 13 | 3 | 3 | 9 | "3/9" | 2 |
1 | b | 14 | 4 | 4 | 9 | "4/9" | 1 |
2 | c | 12 | 1 | 1 | 10 | "1/10" | 4 |
2 | c | 13 | 2 | 2 | 10 | "2/10" | 3 |
2 | c | 14 | 3 | 3 | 10 | "3/10" | 2 |
2 | c | 15 | 4 | 4 | 10 | "4/10" | 1 |
2 | d | 12 | 1 | 1 | 6 | "1/6" | 3 |
2 | d | 13 | 2 | 2 | 6 | "2/6" | 2 |
2 | d | 14 | 3 | 3 | 6 | "3/6" | 1 |
If i doesn't understand correctly, please clear me.
You could give me an example like how i show you so i can understand correctly and quickly.
Best Regards
Maggie
Hello,
This is a follow up and this problem still pending.
I have prepared an case of r BI Desktop Report with more testing data available for you to help me figure out the issue. Here is the details.
The report is to display the Cases by Top 3 Products (%) for a Firm in Banking Service’ industry or ‘Investment’ industry vs. all firms in that same industry. I would like to need your help to check why the Industry’s top 3 products are not always displaying correctly. I have attached a power bi test case where more details are available.
1. Entity Relationship Diagram:
2.
,
DAX Calculations:
FIRM_Total of Case = calculate( DISTINCTCOUNT(‘Case’[Case Title]), filter (‘Case’, ‘Case’[Date Completed]<>blank() )) Firm_Total of Case AllProd = Calculate( [FIRM_Total of Case], all(‘Case’[Product])) FIRM (TOP 3 Product%) = divide ( [FIRM_Total of Case], [Firm_Total of Case AllProd] ) RankProductFirm = if([FIRM (TOP 3 Product%)]<>blank(), (Rankx( ALLselected(‘Case’[Product]), ‘Key Measurements’[FIRM (TOP 3 Product%)], ,DESC)), 4) Sector (Closed)_RevProd = VAR SECTOR= CALCULATETABLE( VALUES(‘Case’[Industry]), ALL(‘DataTable’), aLL(‘Case’[Product]) ) RETURN Calculate ([FIRM_Total of Case], All(‘Case’[Firm]), SECTOR) SECTOR_AllProd = Var Sector= Calculatetable (Values(‘Case’[Industry]), All(‘DataTable’) ) Return Calculate ([FIRM_Total of Case], All(‘Case’[Firm],‘Case’[Product]), sector) SECTOR (Top 3 Product%) = DIVIDE ([Sector (Closed)_RevProd],[SECTOR_AllProd] ) RankProductSector = if([SECTOR (Top 3 Product%)]<>0, (Rankx( ALL(‘Case’[Product]), ‘Key Measurements’[SECTOR (Top 3 Product%)], ,DESC)), 4) TopProduct = min( [RankProductFirm], [RankProductSector] )Matrix Report Top 3 Product(%) for the firm vs. all firms in the same industry:
I have below two examples to display the report.
3.1 The 'Excellent Bank’
the firm’s top 3 and the Industry’s top 3 are all display correctly - Top 3 products for the Excellent Bank, and top 3 for the Industry (or sector) are all showing up.
3.2 Best Bank
But the ‘Best Bank’ - the industry’s top 3 are not properly displayed. For example, 'Mortage' and 'Credit Card' are missing for both of Year 2019 and Year 2018.
4. Questions:
I am wondering why the Best Bank is not display top 3 prod correctly? Is it because the Firm column does not have as much record? and How to fix? I am attaching the Power BI reports for more details.
Thank you.
I would like to attach a file but wondering how to do it....
https://drive.google.com/file/d/1Kxs8mSlkrIRAE7HaZsmwbfJevS9PrQt3/view?usp=sharing
hope this link works. thank you.
Hi @Anonymous
I missed your post before for i was on vacation.
Now i will try my best to work on this problem.
But to make this problem sloved more quicklly, i suggest you to post a new one and share the link here so more people will see your problem.
This post is old and less people can find it.
Best Regards
Maggie
More information:
The Power BI Reports are displayed on the Portal Website as i-Frame.
The Portal User selects which "Firm" to display and then Portal pass the Firm Name to filter the Power BI report to display the Firm's "Top 3 Product" as well as the "Sector Top 3" statistics of all the firms in same sector. Dimensions include Sector, Product, as well as date.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |