Forum Discussion
Solved
Difference between 2 bars
Hello, I need help in creating something similar (kind of waterfall model) visual in PBI. where we want to showcase the difference between 2 bars like this. Data contains more than 1000 rows so I h...
itsmeanuj My guy. You just take the Diff measure and break it into 3 measures, one for each difference value. PBIX is attached below signature.
IP Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "IP Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "IP"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC1"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result FC1 Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "FC1 Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "FC1"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC2"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result FC2 Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "FC2 Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "FC2"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC3"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result
Greg_Deckler - I am confused about how to create a different measures for each Diff value since we have Diff and Previous measures. do you mean to say we have to create these 2 measures for each value? IF yo could just share an example fo 1 value in PBI, then i will be able to replicate it.
Thanks,
Anuj
Greg_Deckler - Thank you! you are a genius. 🙂
itsmeanuj My guy. You just take the Diff measure and break it into 3 measures, one for each difference value. PBIX is attached below signature.
IP Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "IP Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "IP"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC1"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result FC1 Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "FC1 Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "FC1"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC2"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result FC2 Diff = VAR __Axis = MAX('Versions'[Version]) VAR __Result = SWITCH(__Axis, "FC2 Diff", VAR __1 = SUMX(FILTER('Table',[Versions] = "FC2"),[Value]) VAR __2 = SUMX(FILTER('Table',[Versions] = "FC3"),[Value]) VAR __Result = IF(__1 < __2, __2 - __1, __1 - __2) RETURN __Result, BLANK() ) RETURN __Result