Calculation Help

[nmck86]

Hi All,

I am trying to build a data set that is by column for a heat map build and I am running into issues getting the calculations to show up how I really need them to. I am attaching a pbix file here for your review through google cloud. I think that will make explaining easier. So essentially I want to use a relationship between sheet2 (2) for the column score guide to get all the data in consolidated report. I want to have the scores and then based on the calculations I have created I want %'s for each metric. The calculations are based on 3 categories (high, low, neutral) and then I am giving each category a weight and then based on that I have a % score for each category based on the score (0-10). Below is a screenshot of metric 1 and metric 1 scores... However, I am unable to get the formula I created to work if I add multiple metrics into the data set. I think I am using the wrong calculations as they are not filtering the records based on column name. The 2nd visual is what I want; however, I want it in one single visual as opposed to seperate tables.

https://drive.google.com/open?id=1mSopjlD9DtDh_sscTAHXXIDkEj5bpxvE

Score Guide, Metric 1, Metric 2, Metric 3, Metric 4, Metric 5... so forth.

correct results; however, I have them in 2 different tables

[v-ljerr-msft · Accepted solution]

Hi @nmck86,

Based on my test, you should be able to firstly unpivot the Metric columns(Metric 1, Metric 2, Metric 3, Metric 4, Metric 5 etc). For more details about how to unpivot with Power BI Desktop, you can refer to this article.

Then you should be able to show Value columns as Rows, Attribute column as Columns, and the Metric Scores measure as Values on Matrix visual to get the expected result in your scenario.

Here is the modified pbix file for your reference. 

Regards

[nmck86]

You must have read my mind. This is exactly what I needed up doing yesterday before receiving this amazing information and worked perfectly! Thanks for sharing as this is a good bit of helpful info for anyone else who may struggle with NPS.

[nmck86]

I am trying to get my net promotor score to calculate by column. I have used all the below formulas... However, when I use the Metric 1 Scores it only works if I am looking at one column at a time. I need to put each of my results columns side by side with their respective percentages. I am showing a visual below that shows what I want high level.. However, Please note that the Metric 1 Score really represents NPS by category... 

This is the entire dataset. I am trying to get this data set to use the below formulas and I want it to give me NPS % by category. NPS and Metric 1 Scores really mean the same thing. 

Metric 1 Scores = DIVIDE([High]-[Low],[High]+[Neutrals]+[Low])

High = CALCULATE(COUNT('Consolidated Report'[Likeliness to Recommend]),'Consolidated Report'[Likeliness to Recommend] >=9)

Low = CALCULATE(COUNT('Consolidated Report'[Likeliness to Recommend]),'Consolidated Report'[Likeliness to Recommend] <=6,
'Consolidated Report'[Likeliness to Recommend] >=0)

Neutrals = CALCULATE(COUNT('Consolidated Report'[Likeliness to Recommend]),'Consolidated Report'[Likeliness to Recommend] <=8,
'Consolidated Report'[Likeliness to Recommend] >=7)

https://drive.google.com/open?id=1mSopjlD9DtDh_sscTAHXXIDkEj5bpxvE

[Yggdrasill]

I think what you're trying to to is that you want to Unpivot all the Metrics in Consolidate Report

Go in Query Editor and select the Consolidate Report table and then multi-select all the metrics columns 1-13
Then click Unpivot Columns

You will get 2 columns (Attribute & Values). The Attributes represent all the 13 metrics and the Values are the score for each metric respectively.

You can then create a relationship between sheet2 and Consolidate Report table like so

I can't edit the tables for you in Query Editor since you're using external data source wich I don't have access to

[nmck86]

In the screenshot below I have created a metric score 1 (really is net promotor score); however, I need that calculation for about 13 other metrics. Is there an easy way to do this? So I would want to use the score guide table to build a relationship between the metrics 1-13 and then I want the scores to show up across the entire data set. So essentially I want Metric 1 Scores, Metric 2 Scores, Metric 3 scores, etc.. and then the 0-10 scoring graph on there one single time for all the scores as they have the same calculations just under different metrics. Any insight would be amazing. I have created dummy data to get assistance with the report. If anyone knows about net promotor scores the score can go across multiple categories and that is what the metrics 1-13 represent and the metric score 1 represents the NPS; however, I can't get all the metrics and the NPS score to calculate on the same table. It only works one column at a time. In the screen shot below I have 2 tables showing with different Metric 1 scores because it is looking at different columns. Is there a way to get it all on one table? All formulas are in the PBIX file attached for download.

https://drive.google.com/open?id=1mSopjlD9DtDh_sscTAHXXIDkEj5bpxvE