Matrix - multiple numerical formats for Value from single measure

[mrothschild]

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!

[jdbuchanan71]

@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]

@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 · Accepted solution]

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],"$#,#")
)

[mrothschild]

@jdbuchanan71 

Is there a better framing for:

VAR Result = 
    
        [Upside] +
        [Actual] +
        [Expected] +
        [Downside] +
        _Y_Y_pct_chg 

 While it creates the right output in my matrix, I think it's wreaking havoc with the line chart I'm trying to create from it.

THanks!

[mrothschild]

Perfect!

Thanks!