Difference between 2 bars

[itsmeanuj]

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 have placed it here as Sample Data. 

Sample data looks like this

Cost Center Level ID	Versions	Time Period	Value
10004000	FC3	Jan-2022	6,226
10004000	FC3	Feb-2022	5,685
10004000	FC3	Mar-2022	714,108
10004000	FC3	Apr-2022	9,382
10004000	FC3	May-2022	11,524
10004000	FC3	Jun-2022	689,278

[Greg_Deckler]

@itsmeanuj If the standard waterfall chart won't work you'll need to check out the App Store for custom visuals that may work or use something like Deneb custom visual. 

[itsmeanuj]

@Greg_Deckler - So is it not possible to play around with some measures to show data in this visual format? like the difference between 2 bars?

[Greg_Deckler]

@itsmeanuj Here you go. PBIX is attached beneath signature.

[itsmeanuj]

Thank you @Greg_Deckler . This is helpful. But if you see the sample visual i shared,  I am actually looking for differences in IP, FC1, FC2, FC3. 

[Greg_Deckler]

@itsmeanuj That's great but you didn't provide sample data that included different Versions.

[itsmeanuj]

@Greg_Deckler - Actually i have uploaded it on the google drive and share the link in my original post. Here is the link?

https://docs.google.com/spreadsheets/d/1UaVzfxFvOK2JuyA6LRLZOAjdbqED1x-U/edit?usp=share_link&ouid=10...

[Greg_Deckler]

@itsmeanuj Updated PBIX attached below signature.

[itsmeanuj]

Thank you so much @Greg_Deckler - this worked. Is there a way we can change the colors of the Bars IP, FC1, FC2, and FC3 instead of having just 1 color for them?

[Greg_Deckler]

@itsmeanuj Yes you could if you create a separate measure for each difference value, which wouldn't be too bad. Then you could set a separate color for each.

[itsmeanuj]

@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

[itsmeanuj]

Hi @Greg_Deckler - Any inputs on this would be really helpful.

Thanks,
Anuj

[Greg_Deckler · Accepted solution]

@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

[itsmeanuj]

@Greg_Deckler - Thank you! you are a genius. 🙂

[Greg_Deckler]

@itsmeanuj Missed that link, let me take a look. But, I mean, it's the same exact technique you just have to adjust the category is all. Instead of Month use Version.

[Greg_Deckler]

@itsmeanuj Oh, it might be possible given a disconnected table, a stacked bar chart and a fancy measure but it's not going to be easy. I'll see if I can pull something out of the hat.