Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am in need of getting help with calculating the CX % in the attached PBIX file…. I am also attaching the excel file with the SUMIF that was originally used to get the result set. I have tried everything I know how to get this to work but have been unsuccessful. Please note, I did unpivot my data set because I needed it in a better format to accomplish all the other formulas I needed to create. Any assistance is GREATLY appreciated.
The formula in excel is: =IFERROR((SUMIFS('Sample Data'!$D:$D,'Sample Data'!$A:$A,">=9",'Sample Data'!B:B,'Expected Results'!$E$8)-(SUMIFS('Sample Data'!D:D,'Sample Data'!$A:$A,"<=6",'Sample Data'!B:B,'Expected Results'!$E$8)))/SUMIFS('Sample Data'!$D:$D,'Sample Data'!B:B,'Expected Results'!$E$8),"No Data")
https://drive.google.com/open?id=1HN8Z1COYbUMyT2rptIkHfWd6RupbvHuN
Also, I am attaching the excel file and the pbix file so that you can see the issue I am facing. Any help would be great!!!
Solved! Go to Solution.
Hi @nmck86,
Please try below formula to calculate cx percent:
Percent = VAR _rank = SELECTEDVALUE ( 'Table'[Value] ) VAR _attr = SELECTEDVALUE ( 'Sample Data'[Attribute] ) VAR fitler_price = FILTER ( ALL ( Orignal ), [water price] = _rank ) VAR fitler_quality = FILTER ( ALL ( Orignal ), [water quality] = _rank ) RETURN SWITCH ( _attr, "water price", DIVIDE ( COUNTROWS ( FILTER ( fitler_price, [likeliness to recommend] >= 9 ) ) - COUNTROWS ( FILTER ( fitler_price, [likeliness to recommend] <= 6 ) ), COUNTROWS ( fitler_price ) ), "water quality", DIVIDE ( COUNTROWS ( FILTER ( fitler_quality, [likeliness to recommend] >= 9 ) ) - COUNTROWS ( FILTER ( fitler_quality, [likeliness to recommend] <= 6 ) ), COUNTROWS ( fitler_quality ) ), 0 )
Regards,
Xiaoxin Sheng
Hi @nmck86,
Please try below formula to calculate cx percent:
Percent = VAR _rank = SELECTEDVALUE ( 'Table'[Value] ) VAR _attr = SELECTEDVALUE ( 'Sample Data'[Attribute] ) VAR fitler_price = FILTER ( ALL ( Orignal ), [water price] = _rank ) VAR fitler_quality = FILTER ( ALL ( Orignal ), [water quality] = _rank ) RETURN SWITCH ( _attr, "water price", DIVIDE ( COUNTROWS ( FILTER ( fitler_price, [likeliness to recommend] >= 9 ) ) - COUNTROWS ( FILTER ( fitler_price, [likeliness to recommend] <= 6 ) ), COUNTROWS ( fitler_price ) ), "water quality", DIVIDE ( COUNTROWS ( FILTER ( fitler_quality, [likeliness to recommend] >= 9 ) ) - COUNTROWS ( FILTER ( fitler_quality, [likeliness to recommend] <= 6 ) ), COUNTROWS ( fitler_quality ) ), 0 )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |