topic Re: How to combine two measure into one in a Matrix View in DAX Commands and Tips

How to combine two measure into one in a Matrix View

Anonymous — Tue, 19 Mar 2024 16:00:15 GMT

Hello PBI Community Good Day!

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 Shot 1******

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 the measues for Sales and Revenue:

Much appreciate your time per this matter!

Thank you

Re: How to combine two measure into one in a Matrix View

Greg_Deckler — Tue, 19 Mar 2024 16:37:33 GMT

Anonymous Take Values out of your Columns field well?

Re: How to combine two measure into one in a Matrix View

Anonymous — Tue, 19 Mar 2024 17:57:12 GMT

Hello Greg_Deckler

Not sure what you mean per you comment below.

Take Values out of your Columns field well?

Also, How can I upload a sample .pbix?

Here are my measures. Can you provide an example?

Thank you

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" )

Re: How to combine two measure into one in a Matrix View

Greg_Deckler — Tue, 19 Mar 2024 18:57:53 GMT

Anonymous In your Excel pivot table you have 2 things in the Columns field well, Values and Year. I am saying take Values out of that field well.

Re: How to combine two measure into one in a Matrix View

Anonymous — Mon, 25 Mar 2024 16:30:59 GMT

Hello Greg_Deckler

Good Day!

When I take out value from the column field well, my Total Sales and Total Revenue numbers disappear.

see screen shots below

Note: I'm creating my pivot in Analyze in Excel

Before

After: