Translating an Excel Formula into Power BI DAX

[nmck86]

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://docs.google.com/file/d/1-aXWaQbD_2Fh4ts6w51KhjQ53fWfbMFa/edit?usp=docslist_api&filetype=msex...

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!!!

[Anonymous · Accepted 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