Cases Matrix of Top 3 Products for the Firm vs. all firms in same Industry (.pbix file is attached)

Power BI
1

Hey,
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 ,

  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]
)

  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.

Excellent
Excellent.png896×640 71.2 KB

3.2 Best Bank
But the ‘Best Bank’ - the industry’s top 3 seems not correct.

Best
Best.png936×583 42.4 KB

4**. Questions:** I am wondering why the Best Bank is not display top 3 prod correctly? How to fix? I am attaching the Power BI reports for more details.

2

I have a Power BI report attached.
Firm & Industry Top 3 Product.pbix (415.6 KB)

3

Hi there,

Unfortunately the question you’ve asked here is kind of going over the consulting barrier that we attempt to prevent here.

Please review the post here around how to ask appropriate questions.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

The likely reason for not getting an answer quicker is that the question is to complex and probably requires to much time to understand everything involved and give a solid answer.

I recommend breaking this down into individual question where the community can provide assistance step by step.

When I look at this there is a lot of information but it’s difficult to look at it all and understand where things could be going wrong.

I suggest really focussing in on one formula that is causing an issue and solving one problem at a time. This will also help you greatly in understanding where things could be falling over as well.

Thanks
Sam

4

Also check out here on how to add DAX formula into forum threads.

https://forum.enterprisedna.co/t/how-place-dax-formula-into-forum-topics-posts/156/4

Thanks

7

@Wang123 what did you use to create the graphics? and the comments.

8

Check out a technology called snagit