Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello PBI Community
Good Day!
Sample .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
Solved! Go to Solution.
have you tried:
Total Amount = [Total Sales] + [Total Revenue]
?
SWITCH Statement =
VAR SnRSelected = SELECTEDVALUE(SnR[SnR ])
RETURN
IF(
ISBLANK(SnRSelected),
BLANK(),
SWITCH(
TRUE(),
SnRSelected = "Revenue", [Total Revenue],
SnRSelected = "Sales", [Total Sales]
)
)
SWITCH Statement =
VAR SnRSelected = SELECTEDVALUE(SnR[SnR ])
RETURN
IF(
ISBLANK(SnRSelected),
BLANK(),
SWITCH(
TRUE(),
SnRSelected = "Revenue", [Total Revenue],
SnRSelected = "Sales", [Total Sales]
)
)
rachaelwalker
Success, much appreciate your help!
Thank you
Hello Rachael
Love the switch statement, will give it a try.
Much appreciate your quick response
Thank you
Tamerj1
Success, much appreciate your help!
Thank you
Hello tamerj1
Not yet, will give it a try!
Much appreciate your quick response
Thank you
I like your simple solution. My proposed solution was more complicated than needed.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |