CODEWITHSUNDEEP
visual-studiossasmdxolapcube
Marcin
Marcin

Reputation: 133

Create new calculated measure based on fact table column

I've been assigned to calculate NPS (net promoter score) in our cube. Turns out I'm not entirely sure how to approach it. In our fact table we have NPS answer for each sold item (ranging from 0 to 10 and with NULL when there's no answer). The formula is: % of promoters (>=8) - % of detractors (<=6).

I wanted to create calculated member, but it appears to me, that it can only be created on already existing measure. And NPS cannot be pre-aggregated in any way before further calculations as detailed data must be accessible for the calculations. Moreover, this measure must span accross all the dimensions, like any other regular measure.

Where should I start? All the answers seem to create measure on specified hierarchy/dimension or are based on already existing measure. What I need is something like below:

CREATE MEMBER CURRENTCUBE.Measures.NPS_score AS 

(COUNT(CASE WHEN [Product].[nps_rating] >= 9 THEN [Product].[nps_rating] ELSE NULL END)/COUNT(CASE WHEN [Product].[nps_rating] >= 0 THEN [Product].[nps_rating] ELSE NULL END))
-
(COUNT(CASE WHEN [Product].[nps_rating] <=6 THEN [Product].[nps_rating] ELSE NULL END)/COUNT(CASE WHEN [Product].[nps_rating] >= 0 THEN [Product].[nps_rating] ELSE NULL END))

where nps_rating is fact table (Product) column that I want my calculations to be based on.

Upvotes: 0

Views: 1169

Answers (2)

Marcin
Marcin

Reputation: 133

What I did was:

  • add calculated column in each partition of fact tables for both: NPS_promoters (>=9) and NPS_detractors (<=6) - in DSV (Data Source View),
  • added new measure in cube structure that would count non-empty values on NPS column (so I get count of all the answers),
  • finally in Calculations I've added New Calculated Member with expression below:
[Measures].[NPS Promoter]/[Measures].[NPS_answers] - [Measures].[NPS Detractor]/[Measures].[NPS_answers]

Upvotes: 0

MoazRub
MoazRub

Reputation: 2911

You can create it as a calculated column in you DSV. DSV will acts as a view on your tables. So this will help you get it up quickly.This new calculated column will be avaliable in measures.

go to solution explorer

enter image description here

You will see all your dimensions and facts

enter image description here

Now right click on your fact and click named calcution .

enter image description here

Upvotes: 1

Related Questions