Comparable Bar Chart (Customer Vs Industry) by quarters

Power BI Data Model
1

Hello,
My ‘Case’ query has Case Number, Customer, Industry, Date Inquiry Received, Date Case Created.

1_Case%20Table_added%20Conditional%20Field
1_Case Table_added Conditional Field.png1441×422 52.5 KB

I created below DAX first:

1. Search Date = if('Case'[Date_Inquiry Received]=blank(), 'Case'[File_Creation],'Case'[Date_Inquiry Received]);
2. Quarter = CONCATENATE("Q",ROUNDUP(MONTH([Search Date])/3,0)& " "& ('Case'[Year]))
3. 1_CaseCountByCustomer = CALCULATE (
COUNTROWS('Case'),   ALLEXCEPT('Case', 'Case'[Customer],'Case'[Quarter])
)
4.2_CaseCountByIndustry = 
    CALCULATE (
COUNTROWS('Case'),
   ALLEXCEPT('Case', 'Case'[Industry],'Case'[Quarter])
)

Questions: I would like to display the number of cases by customer Vs Number of Cases by Customer’s industry of Current QuarterTD + 4 previous Quarters. How to achieve in bar chart dynamically?

2_Customer%20Vs%20Industry%20by%20Quarter
2_Customer Vs Industry by Quarter.png1320×657 18.8 KB

2

I have modified the measure and now I got a new visualization:

  * "Year & Quarter" = 'Case'[Year]&"-"&'Case'[Quarter]
  * "11_CaseCountByCustomer" = 
             CALCULATE (
                  COUNTROWS('Case'),
                   ALLEXCEPT('Case', 'Case'[Customer],'Case'[Year & Quarter])
             )
   * 11_CaseCountByCustomer = 
       CALCULATE (
          COUNTROWS('Case'),
             ALLEXCEPT('Case', 'Case'[Customer],'Case'[Year & Quarter])
       )

3_ByCustoemrIndustry_Oct15th
3_ByCustoemrIndustry_Oct15th.png1281×667 23 KB

I have question, if I would like to show five quarters rolling, say,
I) Current Quarter ii) 2018-Q3, iii)2018-Q2, iv)2018-Q1, v)2017-Q4. How to achieve this? Thank you.

3

Just been able to review this.

I think that a clean formula like this should complete this for you

CaseCountbyIndustry =
CALCULATE ( COUNTROWS('Case'),
            ALL('Case' ), VALUES( 'Case'[Industry]) )

Then to get the 4 previous quarters you’ll want to use DATESINPERIOD.

This opens up a time window.

Try this and see if you get the intended results

Cast Count - Prior 4 Quarters = 
CALCULATE( [CaseCountbyIndustry],
        DATESINPERIOD( Date, MAX( Date ), -4, QUARTER ) )

I think this should work but you’ll have to test on your data.

This technique is also covered here (around 5 min mark)

4

Thank you.

I am not sure I understand this technique. I tried the new DAX and got different results:

15_CaseCountbyCustomer" = 
                  CALCULATE (COUNTROWS('CASE'),
                                 ALL('CASE'), VALUES('Case'[Customer]))

25_CaseCountbyindustry" = 
         CALCULATE (COUNTROWS('CASE'),
                     ALL('CASE'), VALUES('Case'[Industry]))

31_CaseCountByCustomer-Prior 4 Qter = 
           CALCULATE( [15_CaseCountbyCustomer],
                 DATESINPERIOD('Case'[Search Date], MAX('Case'[Search Date]),-4,   QUARTER))

32_CaseCountByInd-Prior 4 Qter = 
           CALCULATE( [25_CaseCountbyindustry],
                 DATESINPERIOD('Case'[Search Date], MAX('Case'[Search Date]),-4,   QUARTER))

Oct%2017th_Prior4
Oct 17th_Prior4.png1083×647 40.5 KB

5

The expected result should looks like this. So I must have done some calculation wrong. I attached my data model as well for review.

Oct%2017_old%20Calculate
Oct 17_old Calculate.png1106×657 24.5 KB

Oct17%20DataModel
Oct17 DataModel.png1047×701 32 KB

6

Ok there’s a bit going on here that are impacting the results.

Firstly though can you review here how to place formulas into the forum

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

I will update for now. Thanks

7

Why are you using search date in the table here rather than the date column from the date table?

You should always be using this column if you are analysing information over time rather than one from the fact table

I’m a little confused on where we have got to on this.

You suggested you wanted to compare the number of case versus the number of cases by industry over time.

I’m not sure why you decided to now use this formula

15_CaseCountbyCustomer" = 
                  CALCULATE (COUNTROWS('CASE'),
                                 ALL('CASE'), VALUES('Case'[Customer]))

??

I’m sorry I’ve re-read this thread a number of times now and just majorly confused as to what we’re trying to acheive.

Can you add the file so that it can be reviewed and then lay out exactly the calculation you want.

Thanks

Also just another thought, I’m also not a fan of placing number before measures. I’m not sure why you need to do this.

If you want to make things easy to find, think about the measure branching technique and creating multiple measure groups.

This is far better development methodology than the numbers.

8

I would like to achieve a number of visualizations showing an individual customer’s trend data (quarterly or annually) presented side-by-side with comparative aggregated data for all customers in the same industry. I am attaching a sample data excel file for reference. For example,

I) A Bar chart showing count of cases of a Customer (slicer) Vs count of cases of all customers in same Industry (side-by-side) for current Quarter-to-Date, plus previous four quarters (dynamically). This is the questions that I have discussed in this thread.

There are two dates - “Date Inquiry Received” and “Date Case Created”. We will use “Date Inquiry Received” for the calculation. All cases has a created date. If “Date Inquiry Received” is blank, use “Date Case Created” instead. To achieve this logic I create a new column “Search Date”.
Case Search Date = If ("Date Inquiry Received"=Blank(), "Date Case Created", "Date Inquiry Received"

another example,
2) Table showing cases by Top 3 products (by % of total cases) for an individual customer (slicer) Vs top 3 Products( by % of total) for all customers in that same industry.

Oct%2018_top%203%20Product
Oct 18_top 3 Product.png1018×484 38.3 KB

I watched your video on “Rankx considerations - Power BI & DAX Formula Concepts” and have achieved this:

Rankx_Oct18
Rankx_Oct18.png900×314 11.3 KB
.
But I am still working on special scenarios like this - how to display top 3 for both customer and Industry - I am still working how to display top 3 for industry as well :
image
image.png783×336 11.5 KB

Thank you.

9

DataFile_EDNA_181018.xlsx (35.5 KB)

10

Ok so just spent some time getting this setup.

Then I’m just building things out step by step to get to what you need.

Starting very simple with this for Total Cases

Total Cases = DISTINCTCOUNT( 'Cases Data'[Case Number] )

Also instead of the logic you had for the dates you should create a calculated column in my opinion like this.

Case Search Date = IF( ISBLANK( 'Cases Data'[Date_Inquiry Received] ), 'Cases Data'[File_Creation], 'Cases Data'[Date_Inquiry Received] )

image

Then join the date table to this normalized column

image
image.png516×633 10.1 KB

This simplifies things immensely.

Then here’s some other formulas

Total Cases All Customers = 
CALCULATE( [Total Cases], ALL( 'Cases Data'[Customer] ) )

The context to this calculation is key here

Check out how I’ve set this up.

image
image.png550×508 14.3 KB

This is dynamically calculating how many customers cases there are for all customers in the same industry.

For the topn calculations you want to do, the best function to use is TOPN.

For example

Top 3 Customers Cases = 
SUMX( 
    TOPN( 3, VALUES( 'Cases Data'[Customer] ), [Total Cases], DESC ),
        [Total Cases] )

This doesn’t work for me as there are never more than three customers so I presume the data has been made smaller

image
image.png1007×281 11.3 KB

Another example for you

Top 3 Industry Cases = 
SUMX( 
    TOPN( 3, VALUES( 'Cases Data'[Industry] ), [Total Cases], DESC ),
        [Total Cases] )

Have a look through this model. Hopefully you can see how simplified this can be.

See if you can utilize these ideas to get what you need.

Come back if not.

Attached.
Banking customer cases.pbix (185.1 KB)

Chrs

11

Thank you, Sam.

I applied your formula and found it dynamically simplified calculating how many customers cases there are for all customers in the same industry. And this opened the discussion of below new questions:

1a. To present how many customers cases that for all customer in same industry/Sector Vs that customer cases for the current Fiscal Year, and previous 4 fiscal years, I used the hard coding to define the page level filter and would like know how to achieve this current fiscal year + 4 previous FY dynamically?

Page%20Filter_Oct%2022
Page Filter_Oct 22.png1009×496 41.6 KB

Q2: I have to produce three table/ reports based on three different date fields. For example, Total customers cases on "Date Search Inquiry (3) " ; “Total Compensation” on “Date Completed(1)”, and “Total Opened Cases” on “Date Case Opened(2)”. I could not figure out how to set up relationship between Cases ( 3 different time) with Dates table? For example, can I create a second Calendar table?

3%20Date%20Relationship_Oct22
3 Date Relationship_Oct22.png831×666 34.5 KB

Q3: I copied the same key content to calculate the Average and total compensation like this and please help to check if this is correct, thank you.

 Total Compensation = 
            Sum('Cases Data'[Final_Settlement_Amount])

Total Compensation All Customers = 
    Calculate (
        [Total Compensation], All('Cases Data'[Customer]))

    Average Compensation = 
        AVERAGE('Cases Data'[Final SettlementAmount])

Average Compensation All Customers = 
    Calculate (
       [Average Compensation], All('Cases Data'[customer]))

Thank you.

12

Top 3 Product Calculations - below example explains the target report I need to create.

Top 3 Products % for Customer Vs Top 3 Products for All Customer in Same Industry for current fiscal year, plus 4 previous fiscal years ( example of target report)

Top%203%20Target%20Oct%2022
Top 3 Target Oct 22.png905×851 56.4 KB

And this is what I created.

Top%203%20Produc_oct%2022
Top 3 Produc_oct 22.png1087×1490 71.2 KB
.

Questions: how to calculate the top 3 products % of sub-total of each fiscal year (rather than % of column total?), ? And how to display Top 3 for both customer, and all customer in same industry? Many Thanks.

13

Ok bit to all these so will have to make some time to review them all and come back with answer. Will be back on this as soon as possible at my end.

14

Ok just first thing to note,

Can you please review here regarding multiple questions in the one forum post - https://forum.enterprisedna.co/t/how-to-ask-support-questions-on-the-forum/30

The idea is if you have multiple questions on things different to the time of the forum post then I request they are broken out into different topics.

This is for many reasons, one to help with indexing answers for others, two having to understand and answer all these questions at once can take a long time and doesn’t efficiently get you the answers you need, also it enable others to see questions they know and help quicker.

Ok, I’ll list some assistance to your questions below for this one.

Q1

If you want to create a total of 4 FY back then you want to use the DATESINPERIOD function as the filter in calculate.

This open up a time window backwards (or forwards) that can sum up a certain time frame that you need.

Check out how this is done here. It’s the same idea

Q2

You do not need multiple date table and I don’t recommend it. You need to create inactive relationships and then use the USERELATIONSHIP function in your DAX formula.

Check out this clip for more details

Q3

They look correct but honestly it’s very difficult in Power BI to look at just a formula and say definitively if it’s correct because it can depend on many different things.

For example, what is the context of the calculation. That is very important.

Also understand and seeing the relationships in the model.

All this things matter a lot. That is why I highly recommend see how you results change under new context etc.

If they are producing the correct results then probably they are all good, if not they will need to be adjusted. It’s just hard for me to say due to all the variables at play.

For more details on context, there’s a whole section within this course I highly recommend reviewing. It’s really really important stuff.

Last Questions

If you want to calculate as a percentage of the sub total then you need to work out which column to use the ALL function with & which one you need to potentially bring filter back from.

To me it looks like the Year or FY. To bring back filters you add, VALUES( ColumnName ) as a filter inside of calculate. Similar to the first example I gave in this forum post I believe.

Hopefully that give you ideas on what to do from here.

Thanks