Power BI Filter by Measure

Question

I have two tables. On the first table, OnlineSales, each record describes a sale made online (so it has a Customer ID, Address info, Price, Quantity, etc.). On the other table, InStoreSales, each record describes an InStore sale (they are walk-in sales often paid in cash so there is no Customer ID or address info or anything like that).

I have a Line Chart visual that displays the Total Revenue earned by Day of Week (so Revenues earned on Mondays, Tuesdays, Wed, etc.). HOWEVER, this only shows me the Revenue from InStore Sales.

Basically, the visual uses this Measure for the Values section:

Revenue from InStore Sales = SUM(InStore[TotalPrice])

I also have a Measure which calculates the OnlineSales:

Revenue from Online Sales = SUM(OnlineSales[TotalPrice])

So to get the total I can just add the two.

What I am trying to do is create a Filter/Slicer that will give me the option of whether or not to include OnlineSales in the total revenue calculation. The problem is that the Legend for this visual already contains the Day of Week.

Any thoughts on how to go about this?

RedVII RedVII · Accepted Answer · 2016-12-12T17:39:48

I would say you should combine the two tables into one. You want both the instore sales and online sales to be in a single table, and have a dimension/column made for the "Sales Type". I imagine the table could look like this:

+-------------+---------+------------+-------+----------+
| Customer ID | Address | Sales Type | Price | Quantity |
+-------------+---------+------------+-------+----------+
|           1 | A       | Online     | $x    | y        |
|           2 | B       | Online     | $x    | y        |
|           2 | B       | Instore    | $x    | y        |
+-------------+---------+------------+-------+----------+

So now in the same table, you can tell that customer #1 made an online order, and customer #2 made both an online and an in-store order. Now you can drag the "Sales Type" column into a slicer, and take it from there.

You will also no longer need 2 measures - you will have just 1 measure for aggregating sales. You can either combine these tables in the SQL (if you are extracting from a warehouse) or in Power Query, OR calculate a new table using the DAX SUMMARIZE() feature - get creative.

Power BI Filter by Measure

1 Answers