How to combine two dax measures into one in a matrix view

[PBI-V_Curious]

Hello PBI Community 
Good Day!

Sample .pbix

Power BI Sample Data.pbix

Per the screen shots from my sample .pbix below, I have created a view with Sales and Revenue in a matrix visual with ease. However, when I use Analyze in Excel to create the same view, I can’t use the SnR Amount, I have to use my ( Total Sales and Total Revenue) Key measures I have created.

Screen Shot1

What I’m trying to accomplish:

To create the same view per my .pbix above in Analyze in Excel.

What I have accomplished so far.

I have been able to create a similar view in Analyze in Excel, but my Sales and Revenue are staggered.- Screen Shot 2

I need help with creating one measure that combines both measures to give me the view I have in Screen Shot 1 ( Sales and Revenue stacked and align under each other).

Screen Shot 2

Here are my measure from my sample dataset.

Total Revenue = CALCULATE(SUM(‘SnR 2020 - 2022’[SnR Amount]),‘SnR 2020 - 2022’[transaction_type] = “Revenue”)

Total Sales = CALCULATE(SUM(‘SnR 2020 - 2022’[SnR Amount]),‘SnR 2020 - 2022’[transaction_type] = “Sales” || ‘SnR 2020 - 2022’[transaction_type] = “Timing” )

Please advise if you have any questions.

Much appreciate your time and support per this matter.

Thank you

Attachment:

Sample .pbix

[tamerj1 · Accepted solution]

Hi @PBI-V_Curious 

have you tried:

Total Amount = [Total Sales] + [Total Revenue]

?

[rachaelwalker · Accepted solution]

SWITCH Statement = 
VAR SnRSelected = SELECTEDVALUE(SnR[SnR ])

RETURN
IF(
    ISBLANK(SnRSelected), 
    BLANK(),
    SWITCH(
        TRUE(),
        SnRSelected = "Revenue", [Total Revenue],
        SnRSelected = "Sales", [Total Sales]
    )
)

[rachaelwalker · Accepted solution]

SWITCH Statement = 
VAR SnRSelected = SELECTEDVALUE(SnR[SnR ])

RETURN
IF(
    ISBLANK(SnRSelected), 
    BLANK(),
    SWITCH(
        TRUE(),
        SnRSelected = "Revenue", [Total Revenue],
        SnRSelected = "Sales", [Total Sales]
    )
)

[PBI-V_Curious]

rachaelwalker

Success, much appreciate your help!

Thank you

[PBI-V_Curious]

Hello Rachael

Love the switch statement, will give it a try.

Much appreciate your quick response

Thank you

[tamerj1 · Accepted solution]

Hi @PBI-V_Curious 

have you tried:

Total Amount = [Total Sales] + [Total Revenue]

?

[PBI-V_Curious · Accepted solution]

Tamerj1

Success, much appreciate your help!

Thank you

[PBI-V_Curious]

Hello tamerj1

Not yet, will give it a try!

Much appreciate your quick response

Thank you

[rachaelwalker]

I like your simple solution. My proposed solution was more complicated than needed.