Power Bi Challenge - Round1

[tamerj1]

Hello Community Heroes!

The challenge is to create a report of one simple native matrix visual along with a set of slicers. The purpose of the report is to visualize how different brands are performing against each other in different stores. The user is supposed to select a measure (for example Sales Amount) then select 3 brands from 3 different slicers and the matrix visual should display the values of the selected measure (Sales Amount) over the 3 selected brands by store.

The names of the selected brands should change dynamically with different selections.
For the comparison, three percentage difference values need to be displayed along with the 3 brands. The percentage difference is defined as (Brand Value / Reference Brand Value) - 1:

∆% 1 = (Brand2 / Brand1) - 1

∆% 2 = (Brand3 / Brand1) - 1

∆% 3 = (Brand3 / Brand2) - 1

The displayed name of the percentage differences should also change dynamically with the selection.

Except of using a native power bi matrix visual, there are no specific rules on how to achieve the intended result. You are free to do it entirety with DAX measures, calculation groups, field parameter or relay on readjusting the data model using either DAX, power query or SQL or any combination of these tools. It would be also great if someone can find a solution using python or R. The purpose of this is to enrich the collective knowledge of the community and any one who reads this post. 

Attached the standard Contoso sample file. Also feel free to use any sample file upon your comfort.

@lbendlin  @Greg_Deckler @Jihwan_Kim  @sjoerdvn @OwenAuger @DataNinja777  @quantumudit @ThxAlot @johnbasha33 @Dangar332 @ValtteriN @AntrikshSharma @AlexisOlson @Martin_D 

[Paul-Wyatt]

@tamerj1 & @AlexisOlson 

To you both.  What an astounding modelling solution.  As a new developer on the PBI platform, I am blown away at the versatility provided by Power BI, and your solutions to the set challenge have really opened my mind as to the art of the possible.  I had no idea that such a matrix design was possible without resorting to custom visuals.  I now look forward to building both solutions , step-by-step, to extract as much learning and technique as I can. 

Thank you so much for giving your time and the products of your exceptional abilities so freely.  You are truly inspirational. 

[tamerj1]

Thank you @Paul-Wyatt 
That was exactly the purpose of this challenge! Glad that you found it helpful.

[AlexisOlson · Accepted solution]

My submission is attached.

(Edited to hack the sort order to match specifications.)

[tamerj1]

@AlexisOlson 
That is absolutely brilliant!! Knowing how amazing you are in Power Query, I was expecting you to build the Brands table using Power Query but you did it with DAX and you've smoothly transferred the filter using TREATAS. I feel extremely happy that I'm able to exchange knowledge with someone who is a next level comprehensive school! Thank you so very much for sharing your knowledge.
Here attached my solution which is very similar to yours except that I'm relying on a physical relationship to transfer the filter pushing most of the DAX to the table query. Also I have added aditional filters to exclude the brands that are selected in the other slicers.

[AlexisOlson]

@tamerj1 When working with a PBIX that is shared online, I don't often use Power Query since the data sources aren't usually included so tinkering with the queries isn't much of an option. Also, cross-joins are easier in DAX than M, though I plan to write some custom functions to remedy that and submit them here at some point:
https://github.com/OscarValerock/PowerQueryFunctions

I'm a bit disappointed we had such similar solutions, but maybe that's just what the obvious one is. Part of the fun of such things is seeing wildly different ways of achieving similar things. If anyone else can think of a different approach, I'd love to see it.

[tamerj1]

@AlexisOlson 

Wow!! 
Looks perfect! Let me have a look at it tomorrow morning. It is already 9:30pm here 😅

[johnbasha33]

@tamerj1 

here is my thought on this, correct me if i am wrong. 

we can modify the data model or use DAX measures to dynamically reorder the brands based on the user's selection. Here's how you can do it:

> Ensure that your data model includes a table for "Brands" and establish a relationship with your sales data table.

> Create measures to calculate sales amounts for the selected brands. We'll also create a measure to dynamically rank the selected brands based on the user's selection. Here's an example of how you can create these measures:

```dax
Sales Amount = SUM('Sales'[SalesAmount])

Selected Brand 1 Sales = CALCULATE([Sales Amount], 'Brands'[Brand] = SELECTEDVALUE('Brands1'[Brand]))

Selected Brand 2 Sales = CALCULATE([Sales Amount], 'Brands'[Brand] = SELECTEDVALUE('Brands2'[Brand]))

Selected Brand 3 Sales = CALCULATE([Sales Amount], 'Brands'[Brand] = SELECTEDVALUE('Brands3'[Brand]))

Brand Order =
SWITCH (
SELECTEDVALUE ( 'Brands'[Brand] ),
SELECTEDVALUE ( 'Brands1'[Brand] ), 1,
SELECTEDVALUE ( 'Brands2'[Brand] ), 2,
SELECTEDVALUE ( 'Brands3'[Brand] ), 3
)
```

> Create a matrix visual in Power BI. Add "Store" to rows and "Brand" to columns. Add the selected measure (e.g., "Sales Amount") to values. Sort the "Brand" column by the "Brand Order" measure.

> Use a card visual to display the selected brand names dynamically. Create a measure for each brand name like this:

```dax
Selected Brand 1 Name = SELECTEDVALUE('Brands1'[Brand])
```

Repeat this for each selected brand. Then place these measures in separate cards in your report.

> **Dynamic Percentage Difference Display**: Similarly, create measures for each percentage difference as described in the previous response.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[tamerj1]

Good job @johnbasha33 !
But you have to apply this solution to the sample file to make sure it is working as intended without major issues. Looking forward to receive your working solution!

[lbendlin]

Does it have to be a matrix?  Seems like a chart would be a more appropriate solution unless you want to consciously single out Brand1  (it is compared against, but not the basis, according to your rules). A chart would remove that "unjustice" or skew.

[tamerj1]

@lbendlin 

Let me elaborate on my response to your question.
This challenge was inspired from a real life requirement. Originally, the requirement was to have one brand ("scenario" in the real life requirement) selected as a reference and then another scenraio selected to compare it with the reference scenraio. Then new requirements came up, that is to compare three scenarios at once where the reference scenario is always tye one to the left. In all cases when comparing two scenarios, one of them has to be considered as a reference, this is why the appearance order in the matrix is important. 

However, the solution to this challenge does not have to comply with this. Better ideas are always appreciated. 

[lbendlin]

I would use a single brand slicer with multi choice. Then I would allow the users to choose whatever brands they want to compare. And as I mentioned earlier I would represent the data in a column chart and then let the user make the over/under decisions with their eyeballs.

"Comparing against a base value"  makes sense but it would not need to include pitching brand 2 against brand 3.

[tamerj1]

@lbendlin 

What would be the definition of the formula without a reference value? Using one slicer would make it ambiguous. Not knowing which one is the reference value makes the comparison less meaningful. This is why the requirement above clearly says "3 different slicers". You can however keep only one base scenario with two comparisons, that would also make complete sense. 

[tamerj1]

@lbendlin 
I actually want to see The selected Brand1 first in the matrix, Brand2 2nd and Brand3 3rd. However, I love the idea of the chart. The matrix solution shall be preferred over other visuals, but as long as it is a native visual the solution shall also be acceptable.