Forum Discussion

troyhimes's avatar
troyhimes
Resolver I
4 years ago
Solved

Waterfall data (Value Change) in Matrix?

This seems simple....I want a matrix that contains the same 'Value Change' data that is displayed in the Waterfall tooltips.   Perhaps the only solution is the 'Hybrid Table' (PaulDBrown ) or...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Let's see if this is what you need.

    1) create slicers for the verions (A & B). Join A with an active relatioshionship to the fact table and B via inactive relationship

     

     2) Create the measure for the visual

     

     

    Sum cost (A) = 
SUM(FTable[Cost])
    Sum Cost (B) =
CALCULATE (
    SUM(FTable[Cost]),
    REMOVEFILTERS ( 'VersionA slicer'[VersionA] ),
    USERELATIONSHIP ( FTable[Version], 'VersionB Slicer'[VersonB] )
)

    Difference = 
[Sum cost (A)] - [Sum Cost (B)]
    % Difference = 
DIVIDE([Sum Cost (B)], [Sum cost (A)]) -1

     

     

     

    And to make the slicers user friendly, create this measure to use as a filter in both slicers (set the value to 1)

     

     

    Filter Slicers = 
COUNTROWS(EXCEPT('VersionA slicer','VersionB Slicer'))

     

     

     

     

    I've attached the sample PBIX file

troyhimes's avatar
troyhimes
Resolver I
4 years ago

Hi PaulDBrown , here is a simplified version of the data (Budget Data ).  In the simplified data there are 3 versions of the budget.  In the actual version, by the end of the process there may be 15-20 versions.  The versions are being added 2-3 times a week.  Throughout the duration of the process the waterfall charts are used to visually compare any one of the versions against any of the others.  The tooltips on the waterfall show the 2 values, 2 value difference & % difference.  I'd like a matrix that reflects those tooltip values.... 4 columns (version 1 value, version 2 value, version 1&2 value difference, version 1&2 % difference).

 

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Thanks for the sample. So how do you select which 2 versions must be compared? Are you using a slicer (or 2)?

  • troyhimes's avatar
    troyhimes
    Resolver I
    4 years ago

    Yes, users are using slicers to compare the versions

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      Can you show a snapshot of the relevant model tables? I take it one of the slicers is from an unrelated table, correct?

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Let's see if this is what you need.

        1) create slicers for the verions (A & B). Join A with an active relatioshionship to the fact table and B via inactive relationship

         

         2) Create the measure for the visual

         

         

        Sum cost (A) = 
SUM(FTable[Cost])
        Sum Cost (B) =
CALCULATE (
    SUM(FTable[Cost]),
    REMOVEFILTERS ( 'VersionA slicer'[VersionA] ),
    USERELATIONSHIP ( FTable[Version], 'VersionB Slicer'[VersonB] )
)

        Difference = 
[Sum cost (A)] - [Sum Cost (B)]
        % Difference = 
DIVIDE([Sum Cost (B)], [Sum cost (A)]) -1

         

         

         

        And to make the slicers user friendly, create this measure to use as a filter in both slicers (set the value to 1)

         

         

        Filter Slicers = 
COUNTROWS(EXCEPT('VersionA slicer','VersionB Slicer'))

         

         

         

         

        I've attached the sample PBIX file