Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |