Calculate and display Vendor KPI vs Vendor Industry KPI in one table within Power BI

[bwang123]

Hello All,

I am working on a report to display a vendor KPI for vendor (filter="a") and vendor industry statistic KPI in one table in Power BI.

1. source data example.

2. Vendor KPI example, vendor filter = "a", the Top Issue by % of Total is displayed.

3.  Vendor industry KPI table - Vendor Industry statistics include all vendors information of same industry as vendor "a" .

4.  Desired Final Report - I am working on display both KPI in one table using calcuations within Power BI and I have problems implementing this report and need help.  Vendor Industry is dynamics and changes when a vendor change. For example, if Vendor fileter = d, the Industry KPI shoudl display that for "BabyFood" and how to achieve this within Power BI.

5. Data link: https://drive.google.com/open?id=1Pt97A1o16GQAimQAazIsAZP_bkXxICGE

Thank you

[v-yuezhe-msft · Accepted solution]

@bwang123,

Create the following measure in your table.

Vendor KPI = CALCULATE(COUNTA('Source Data'[Case #]),ALLSELECTED('Source Data'[Vendor]))/CALCULATE(COUNTA('Source Data'[Case #]),ALLEXCEPT('Source Data','Source Data'[Vendor]))

Vendor KPI vs Vendor Industry KPI = CALCULATE(COUNTA('Source Data'[Case #]),ALL('Source Data'[Vendor]))/ CALCULATE(COUNTA('Source Data'[Case #]),ALLEXCEPT('Source Data','Source Data'[Vendor Industry]))

For more details, please review attached PBIX file.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@bwang123,

Create the following measure in your table.

Vendor KPI = CALCULATE(COUNTA('Source Data'[Case #]),ALLSELECTED('Source Data'[Vendor]))/CALCULATE(COUNTA('Source Data'[Case #]),ALLEXCEPT('Source Data','Source Data'[Vendor]))

Vendor KPI vs Vendor Industry KPI = CALCULATE(COUNTA('Source Data'[Case #]),ALL('Source Data'[Vendor]))/ CALCULATE(COUNTA('Source Data'[Case #]),ALLEXCEPT('Source Data','Source Data'[Vendor Industry]))

For more details, please review attached PBIX file.

Regards,
Lydia

[bwang123]

Thank you, v-yuezhe-msft and "Vendor KPI" works well.  More explaination on 'Vendor Industry KPI" is clarified below: 

I would like to calculate Vendor Industry KPI, column "Industry_% of Total Cases" of Table 3 in this way:

step 1=create Pivot Table from source date.  for example, 'nutrition' industry  'label' issue is 18% of total. 

Another example as attached below, for 'BabyFood' industry 'service' issue is about 57% of total.

step 2: we want to have this industry KPI appear on the final report and 'nutrition' industry 'lable' issue of total percentage 18% will remain unchanged when the vendor filed are belong to 'nutrition'.  for example,

 

 another example,

So, my question is, based on above calrification, how to calculate the Vendor Industry KPI?  I am asking becasue I later have different Vendor Industry KPI appear (attaced blow) and I am not sure the reason since i have not changed calcuation. Thank you.

Can you help me with this?  Thank you.  

[v-yuezhe-msft · Accepted solution]

@bwang123,

When you don't select any values in the Vendor Industry slicer,  the Vendor KPI vs Vendor Industry KPI equals to (case counts of each issue type)/(counts of all cases in your table).

You would need to select the value in the Vendor Industry slicer to make the Vendor KPI vs Vendor Industry KPI  measure show correctly as that in your pivot table.

Regards,
Lydia

[bwang123]

Hello All,

I have problems to Display Dynamic Change of "Vendor Industry" KPI based on "Vendor" selection in Power BI and would like to need your help.

1. Source data, fields
• Case #
• Vendor
• Vendor Industry  (One Vendor belong to one Vendor Industry)
• Issue of Cases
• Created On

 

2.  Key Performance Indicator

• Calculate Top Issues by % of Total Cases = (Count of Issue )/Total Count * 100%
• KPI by Vendor
• 

• KPI by Vendor Industry
• 

1. Desired Final Report to display both KPI in one table using calculations in Power BI.

For example, If vendor filter ="a", the Vendor Industry should = 'nutrition',

If vendor filter="d", then Vendor Industry should = "BabyFood".  I cannot figure out how to dynamically change Industry KPI based on vendor selection and need your help.  Thank you.

4 . Data link: https://drive.google.com/open?id=1Pt97A1o16GQAimQAazIsAZP_bkXxICGE