Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
5 years ago
Solved

Matrix - multiple numerical formats for Value from single measure

PBIX File: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing

 

In the screenshot below, the upper panel shows the intended numerical format for the row associated with "Y/Y %".  The upper panel was created using multiple individual [measures].  The lower panel uses a single [measure] determined as follows:

SINGLE_MEASURE = 
    
        [Upside] +
        [Actual] +
        [Expected] +
        [Downside] +
        [Y/Y %]

 

Two questions:

1) How can I change the SINGLE_MEASURE so that the [Y/Y %] row in the matrix is FORMAT( [Y/Y %] , "0.0%; (0.0%)" ?

2) How can I change the SINGLE_MEASURE so that when the row (e.g,. 2007, 2008, 2009. . . ) is collapsed, it displays only [Actual]?

 

Thanks!

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    Sorry, I missed your question #2.

     

    That would be like this.

     

    __SINGLE MEASURE = 

// This takes the individual [MEASURES] named in the disconnected slicer and combined them into a single measure so that the Matrix table will hide rows from appearing
// 
    VAR _MEASURE = 
        DISTINCT('Measure Slicer'[Measure Names])

    VAR _Y_Y_pct_chg = 
        IF(
            "Y/Y %" IN _MEASURE, 
            [Average Annualized Price change (weighted by Model Year Units)] , 
            BLANK()
        ) 

VAR Result = 
    
        [Upside] +
        [Actual] +
        [Expected] +
        [Downside] +
        _Y_Y_pct_chg 
        
RETURN
IF ( 
    HASONEVALUE ( 'Measure Slicer'[Measure Names] ), 
    IF ( 
        SELECTEDVALUE('Measure Slicer'[Measure Names] ) = "Y/Y %",
        FORMAT(Result,"0.0%; (0.0%)"),
        FORMAT(Result,"$#,#")
    ),
    FORMAT([Actual],"$#,#")
)

     

     

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    mrothschild 

    Give it a try like this.

     

    __SINGLE MEASURE = 

// This takes the individual [MEASURES] named in the disconnected slicer and combined them into a single measure so that the Matrix table will hide rows from appearing
// 
    VAR _MEASURE = 
        DISTINCT('Measure Slicer'[Measure Names])

    VAR _Y_Y_pct_chg = 
        IF(
            "Y/Y %" IN _MEASURE, 
            [Average Annualized Price change (weighted by Model Year Units)] , 
            BLANK()
        ) 

VAR Result = 
    
        [Upside] +
        [Actual] +
        [Expected] +
        [Downside] +
        _Y_Y_pct_chg 
        
RETURN
    IF ( 
        SELECTEDVALUE('Measure Slicer'[Measure Names] ) = "Y/Y %",
        FORMAT(Result,"0.0%; (0.0%)"),
        FORMAT(Result,"$#,#")
)

     

     

     

    • mrothschild's avatar
      mrothschild
      Icon for Continued Contributor rankContinued Contributor
      5 years ago

      jdbuchanan71  that works great for the formatting issue!  Thanks!

       

      Now, when I collapse the rows to a single, how can I get the output displayed on the matrix to only be [Actual] if more than one Slicer option is selected?

       

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        5 years ago

        Sorry, I missed your question #2.

         

        That would be like this.

         

        __SINGLE MEASURE = 

// This takes the individual [MEASURES] named in the disconnected slicer and combined them into a single measure so that the Matrix table will hide rows from appearing
// 
    VAR _MEASURE = 
        DISTINCT('Measure Slicer'[Measure Names])

    VAR _Y_Y_pct_chg = 
        IF(
            "Y/Y %" IN _MEASURE, 
            [Average Annualized Price change (weighted by Model Year Units)] , 
            BLANK()
        ) 

VAR Result = 
    
        [Upside] +
        [Actual] +
        [Expected] +
        [Downside] +
        _Y_Y_pct_chg 
        
RETURN
IF ( 
    HASONEVALUE ( 'Measure Slicer'[Measure Names] ), 
    IF ( 
        SELECTEDVALUE('Measure Slicer'[Measure Names] ) = "Y/Y %",
        FORMAT(Result,"0.0%; (0.0%)"),
        FORMAT(Result,"$#,#")
    ),
    FORMAT([Actual],"$#,#")
)