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
Anonymous
Not applicable

Need Help_ Rankx Calculate Top 3 Products for "Firm" and for "Sector" ( firms of the same sector)

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. Connect Power BI to Dynamics 365 online "Cases" entity
  2. Cases Entity(table) has fields like
    Case Number
    Firm Name
    Sector (one firm belong to one Sector only)
    Product
    Date Case Closed
  3. Prepare below DAX calculation
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"

2019-01-24_22-40-56-forum.png

 

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.

 

 

 

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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

 

Anonymous
Not applicable

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:

ER.png

2. 

ER,

  1. 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.

  1. 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.

Excellent.png

 

 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.  

Best.png

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....

 

Anonymous
Not applicable

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

Anonymous
Not applicable

@v-juanli-msft

Thank you, Maggie. 

Please find the example below.  

 2019-01-29_14-15-35 - example.png

 

 2019-01-29_14-36-16 - firm.png

 

 

 2019-01-29_14-36-38 -vis.png

 

 

Anonymous
Not applicable

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.  

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.