DAX how to Calculate Count of cases by customer's industry When using Customer Name in the Slicer

[Wang123]

Hello Fellow Power BI Users -

This question seems an easy calculation but my current DAX generates wrong result and need some help.

Basic Information:

• Query1 = ‘Cases’, Fields= “Customer Name”, “Case Number”, “CRM Sector”, “Issues” and dates fields;
• Query 2 = ‘Dates’
• Query 3= ‘Mapping_Sector’, Fields = “CRM Sector” and “Industry”
• Relationship
  
  

What I would like to accomplish:

1. Calculate the count of cases of the specific customer (By Slicer)

Firm (Count) = calculate(DISTINCTCOUNT('Cases'[Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))

The result is good - Refer to below screen print Count of Cases by Fiscal Year.

2. Calculate the count of cases of the Customer’s Industry - I have problems here

In this example, Customer “Bank of Nova Scotia” (slicer filter value) belongs to “Industry” = “Banking Services”. I need to calculate the count of cases of the Customer’s industry of “Banking Services” but my current DAX calculate the count of all industries as a whole which is not correct.

Sector (count) = Calculate ([Firm (count)], All('Cases'[Customer Name]))

The result is not I expected refer to below screen print:

Thank you.

[Wang123]

Update, I have added “Industry” field to Cases query by merging two tables and created below calculation to count the cases of Customer’s Industry.

Rev_Sector(Count) = Calculate(COUNTROWS('cases'), all('cases'), values('Cases'[Sector (merge)]))

[sam.mckay]

Ok great, yes that that’s the formula I would have advised using.

Thanks

[Wang123]

Unfortunately, the calculation in my second post apparently is Grand Total of all Fiscal Years and is not correct. The correct result should be varied based on different fiscal year. I am wondering how to achieve this?

[PhilC]

@Wang123

Rev_Sector(Count) = Calculate(COUNTROWS(‘cases’), all(‘cases’), values(‘Cases’[Sector (merge)]))

Try using ALLSELECTED(‘cases’), to see if that keeps the filter applied by having Fiscal Year on the column headings.

Cheers
Phil

[Wang123]

Thank you, Unfortunately Allsected still cannot get the expected result. Instead I i am working on Variable to see if it is going to work.

[Wang123]

See below updates

• Use this version to calculate ‘Industry’ aggregate based on Slicer selection

  Firm (Inquiries) = calculate( DISTINCTCOUNT('Cases'[Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
  
    Sector (Inquiries)_Rev 3 = VAR SECTOR=CALCULATETABLE(VALUES('Cases'[Sector Name (merge)]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
        RETURN
        Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR) 
  

• Result is good when Firm is not blank:
  

• Result is not good because then ‘Firm’ is blank then the Industry is blank, this is not correct
  

  

• Any ideas?

[sam.mckay]

Is it possible to see an example file here? Finding this a bit too difficult to work out without seeing everything in play and testing a few options.

Thanks