topic Re: Looking for PoP (Period over Period) Change to Specific Row-Level Data in DAX Commands and Tips

Looking for PoP (Period over Period) Change to Specific Row-Level Data

4rmfunction — Mon, 22 Jul 2024 21:14:29 GMT

Hello,

Thanks for stopping by. I am looking to get the promoter and Detractor percentage variance by quarter and month. I will post the table in a comment.

Using the following measures to aggregate the data I need:

Current totals:

Total Responded =

COUNTA(Table[Column1])

Current Promoter Totals:

Glbl_Promoter Total =

CALCULATE (

COUNTROWS ( Table ),

Table[NPS Group] = "Promoters"

)

Previous Quarter Totals:

CALCULATE (

[Total Responded],

PREVIOUSQUARTER (Date_Table[Date])

)

Previous Quarter Promoters:

Previous Quarter Promoters =

CALCULATE (

COUNTROWS ( Table),

Table[NPS Group] = "Promoters",

PREVIOUSQUARTER(Date_Table[Date])

)

And then a measure to tie everything together:

Glbl_PromoterQuarterVariance =

VAR CurrentQtrPromoter =

DIVIDE(CSAT_GLOBAL[Glbl_Promoter Total],[Total Responded])

VAR PrevQtrPromoter =

DIVIDE([Previous Quarter Promoters], [PreviousQuarterVolume])

VAR _Variance =

DIVIDE(CurrentQtrPromoter - PrevQtrPromoter, PrevQtrPromoter)

RETURN

format(_Variance, "#%") & " " &IF(_Variance > 0, "+")

What I end up with is something like this:

Where it calculates the percentage change of the specific row and not how the row interacts with the overall survey volume. What I need it to do is calculate the specific topic percentage against the total, and then the QoQ change based on that. Example:

Topic (Level 2)	Current Quarter Promoters	Percent of Total	Change	Glbl_PreviousQuarterPromoters	Percent of Total	Total Responded	PreviousQuarterVolume
Shipping	64	15%	17%	99	13%	112	206
Payments	30	7%	55%	35	4%	72	73
Cancellation	12	3%	4%	21	3%	48	62
Returns & Refunds	59	14%	-19%	132	17%	107	225
Promo Code	16	4%	-48%	56	7%	40	103
Account	12	3%	-40%	36	5%	25	54
Status	20	5%	-5%	38	5%	32	67
						436	790

I hope this is enough information. Thanks again in advance.

Re: Looking for PoP (Period over Period) Change to Specific Row-Level Data

4rmfunction — Mon, 22 Jul 2024 21:22:32 GMT

Survey Responded Date	Topic	Topic (Level 2)	NPS Group
01-Jan-24	Product Service	Product Service Request	Neutrals
02-Jan-24	Product Service	Redirect To Form	Promoters
03-Jan-24	Product Service	Limited Warranty Coverage	Promoters
04-Jan-24	Product Service	Product Service Request	Detractors
05-Jan-24	Product Service	Limited Warranty Coverage	Detractors
06-Jan-24	Product Service	Limited Warranty Coverage	Promoters
07-Jan-24	Product Service	Limited Warranty Coverage	Neutrals
25-Jan-24	Product Service	Limited Warranty Coverage	Detractors
26-Jan-24	Product Service	Limited Warranty Coverage	Detractors
27-Jan-24	Product Service	Limited Warranty Coverage	Detractors
28-Jan-24	Product Service	Product Service Status	Detractors
29-Jan-24	Product Service	Limited Warranty Coverage	Promoters
30-Jan-24	Product Service	Product Service Request	Neutrals
01-Feb-24	Product Service	Limited Warranty Coverage	Detractors
02-Feb-24	Product Service	Limited Warranty Coverage	Promoters
03-Feb-24	Product Service	Redirect To Form	Promoters
04-Feb-24	Product Service	Limited Warranty Coverage	Promoters
05-Feb-24	Product Service	Limited Warranty Coverage	Promoters
06-Feb-24	Product Service	Redirect To Form	Detractors
07-Feb-24	Product Service	Product Service Status	Detractors
19-Feb-24	Product Service	Product Service Request	Detractors
22-Feb-24	Product Service	Product Service Request	Neutrals
23-Feb-24	Product Service	Limited Warranty Coverage	Detractors
24-Feb-24	Product Service	Limited Warranty Coverage	Detractors
25-Feb-24	Product Service	Limited Warranty Coverage	Detractors
26-Feb-24	Product Service	Product Service Status	Promoters
27-Feb-24	Product Service	Product Service Status	Detractors
29-Feb-24	Product Service	Product Service Status	Promoters
02-Mar-24	Product Service	Limited Warranty Coverage	Detractors
04-Mar-24	Product Service	Product Service Status	Promoters
11-Mar-24	Product Service	Product Service Request	Detractors
30-Mar-24	Product Service	Product Service Status	Promoters
31-Mar-24	Product Service	Limited Warranty Coverage	Promoters
03-Apr-24	Product Service	Limited Warranty Coverage	Promoters
05-Apr-24	Product Service	Product Service Status	Promoters
06-Apr-24	Product Service	Limited Warranty Coverage	Detractors
18-Apr-24	Product Service	Product Service Request	Promoters
25-Apr-24	Product Service	Limited Warranty Coverage	Promoters
28-Apr-24	Product Service	Limited Warranty Coverage	Promoters
29-Apr-24	Product Service	Product Service Status	Promoters
03-May-24	Product Service	Limited Warranty Coverage	Detractors
21-May-24	Product Service	Product Service Request	Detractors
25-May-24	Product Service	Limited Warranty Coverage	Detractors
26-May-24	Product Service	Limited Warranty Coverage	Promoters
04-Jun-24	Product Service	NA 5	Promoters
06-Jun-24	Product Service	Product Service Status	Promoters
14-Jun-24	Product Service	Limited Warranty Coverage	Promoters
21-Jun-24	Product Service	Product Service Status	Promoters
26-Jun-24	Product Service	Redirect To Form	Promoters
27-Jun-24	Product Service	Limited Warranty Coverage	Neutrals

Re: Looking for PoP (Period over Period) Change to Specific Row-Level Data

Anonymous — Tue, 23 Jul 2024 07:37:28 GMT

Hi 4rmfunction

According to your formula, you seem to use three tables, but I don't know the data structure of your other two tables, so I do it directly in the data table you provided.

In order to better help you, I have another question that I need to confirm with you. What is the calculation logic of these four columns?

What I have done so far is as follows, feel free to let me know if there are any problems.

Create a calculated column as follows

quarter = QUARTER([Survey Responded Date])

Create several measures as follow

CurrentQuarterPromoters = VAR _currentquarter = CALCULATE(MAX([quarter]), ALL('Table')) RETURN CALCULATE(COUNT('Table'[NPS Group]), FILTER('Table', [NPS Group] = "Promoters" && [quarter] = _currentquarter))

Percent of Total = VAR _CurrentQuarter = CALCULATE(MAX([quarter]), ALL('Table')) VAR _count = CALCULATE(COUNT('Table'[NPS Group]), FILTER('Table', [quarter] = _CurrentQuarter)) RETURN DIVIDE([CurrentQuarterPromoters], _count)

PreviousQuarterPromoters = VAR _previousquarter = CALCULATE(MAX([quarter]), ALL('Table')) - 1 RETURN CALCULATE(COUNT('Table'[NPS Group]), FILTER('Table', [NPS Group] = "Promoters" && [quarter] = _previousquarter)) + 0

Percent of Total(previous) = VAR _previousQuarter = CALCULATE(MAX([quarter]), ALL('Table')) - 1 VAR _count = CALCULATE(COUNT('Table'[NPS Group]), FILTER('Table', [quarter] = _previousQuarter)) RETURN DIVIDE([CurrentQuarterPromoters], _count) + 0

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Looking for PoP (Period over Period) Change to Specific Row-Level Data

4rmfunction — Tue, 23 Jul 2024 13:17:46 GMT

Hi Anonymous,

Thanks for your feedback, but it looks like you're getting a similar return to what I was seeing before.

The logic of the table I provided at the end of my message is as follows:

Topic (Level 2)	Current Quarter Promoters	Percent of Total(a)	Change	Glbl_PreviousQuarterPromoters	Percent of Total(b)	Total Responded	PreviousQuarterVolume
	SUM	Current Quarter Promoters/Total Responded	=(percent of Total(a)-Percent of Total(b))/Percent of Total(b)	SUM	Glbl_PreviousQuarterPromoters/PreviousQuarterVolume	SUM	SUM

What I am trying to achieve is replicating the "Change" column, which is a variance of a percentage.

Thanks again for your message.