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

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

    • troyhimes's avatar
      troyhimes
      Resolver I
      4 years ago

      Yes!....exactly what I was looking for in terms of the matrix.  I've never used 'userelationship' so I've learned something that will come in very handy in other models that have multiple date columns. 

       

      The only issue now is that the user can't use those same slicers to filter the waterfall.  I will chew on your pbix file to see if I can't figure something out.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        How are the slicers set up for the waterfall chart?