cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

What IF Parameters not working when unpivoting the Measure to Rows

Hi All

 

I am stuck at a point when I created the output of WhatIF Measure it works fine here :- 

 Slicer XXX = -8Slicer YYY = -10Slicer ZZZ = -11Slicer AAA = -65
     
 FY 2018 FY 2019FY 2020Revised
XXX                  80.00                    85.00                   70.00                     62.00
YYY                  70.00                    85.00                   65.00                     55.00
ZZZ                  80.00                    85.00                   63.00                     52.00
AAA                  80.00                    85.00                     5.00                   (60.00)

 

JFYI Measure Formula = 

SUMX(Total, IF(Total[Financial Item] = "XXX" , FY 2020 +  'Slicer XXX'[Change Value]),IF(Total[Financial Item] = "YYY" , FY 2020 +  'Slicer YYY'[Change Value]) ,IF(Total[Financial Item] = "ZZZ" , FY 2020 +  'Slicer ZZZ'[Change Value]) , IF(Total[Financial Item]= "AAA" , FY 2020 +  'Slicer AAA'[Change Value]) ),FY 2020)))))
 

But when I try to Unpivot Table in the below manner with these Measure it does not update Slicer Value

 

Summarize Table =
VAR Counter = COUNTROWS(FILTERS('P&L'[Business Line]))
VAR T1 = SUMMARIZE(Total,Total[Business Line],Total[Financial Item],"Year","FY 2018", "Value", Total[Total FY 2018])
VAR T2 = SUMMARIZE(Total,Total[Business Line],Total[Financial Item],"Year","FY 2019", "Value", Total[Total FY 2019])
VAR T3 = SUMMARIZE(Total,Total[Business Line],Total[Financial Item],"Year","RFC 2020", "Value", Total[Total RFC 2020])
VAR T4 = SUMMARIZE(Total,Total[Business Line],Total[Financial Item],"Year","Revised RFC 2020", "Value",
SUMX(Total, IF(Total[Financial Item] = "XXX" , FY 2020 +  'Slicer XXX'[Change Value]),IF(Total[Financial Item] = "YYY" , FY 2020 +  'Slicer YYY'[Change Value]) ,IF(Total[Financial Item] = "ZZZ" , FY 2020 +  'Slicer ZZZ'[Change Value]) , IF(Total[Financial Item]= "AAA" , FY 2020 +  'Slicer AAA'[Change Value]) ),FY 2020))))) )
RETURN
UNION(T1,T2,T3,T4)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: What IF Parameters not working when unpivoting the Measure to Rows

@PK_PBIBoss , for unpivot try this

https://radacad.com/pivot-and-unpivot-with-power-bi



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
Highlighted
Super User IX
Super User IX

Re: What IF Parameters not working when unpivoting the Measure to Rows

@PK_PBIBoss , for unpivot try this

https://radacad.com/pivot-and-unpivot-with-power-bi



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors