Seems like it should be simple

[5TimeGrandpa]

This seems like it should be a common thing, but I have searched high and low trying to find anyone doing or even discussing this. How on earth do you present a measure that shows one particular store's information compared to the company as a whole, and have the store be variable? For example, I might have a graph that shows average sales dollars per employee. I then add various percentiles like Median, 90th Percentile, etc. to my graph. Now, for example, I want a measure on the graph that shows the percentile for store "ABC Store #3" along with the other measures. I want ABC Store #3 to be a variable that I can change to show ABC Store #2, or ABC Store #97. 

The closest I have come is by creating a duplicate of the table used for the graph, and then passing a filter to that duplicate table and basing the measure off of the duplicate table. However, the problem then is that none of the other filters implemented through slicers work properly because the slicers only affect the primary table.

Does anyone have any clue on this?

[v-shex-msft]

HI @5TimeGrandpa,

I agree that 'IN' will suitable to filter compare on multiple records.
BTW, you can also use contains function to compare with multiple rows and single row.

Sample: use selected item to filter measure.

Result =
CALCULATE (
    SUM ( Table[Amount] ),
    FILTER (
        ALL ( Table ),
        CONTAINS ( ALLSELECTED ( Table1[Item] ), Table1[Item], 'Table'[Item] )
    )
)

Notice: Table is current table, Table 1[Item] is the source of slicer.

Regards,

Xiaoxin Sheng

[5TimeGrandpa]

I've been looking at both of the offered solutions, and the problem I see is that there is no slicer here that is filtering the data. We are using PowerBI Embedded, and passing a filter via JQuery at runtime. Let me try to explain this in another way, and use an additional column instead of a measure.

Let's say we have a table that is pulling sales by department for a multi-store chain like this:

Store #, Department, Sales

Store 123, Hardware, $1,000

Store 123, Grocery, $2,000

Store 123, Appliances, $3,000

Store 234, Hardware, $2,000

Store 234, Grocery, $3,000

Store 234, Appliances, $4,000

We have one column in our report that shows Average Sales across stores. So our average is summed at the store # level.

$15,000 (SUM of all sales) / 2 stores (COUNT DISTINCT of Store #) = $7,500 <-- Column 1

Now, so that we can compare the sales of an individual store to the overall average, we need to add a column to the report to show only the total sales for Store 123 ($6,000) or Store 234 ($9,000) depending on which is passed in via a filter.

Then to make it more complex, we have a slicer for Department that should then allow us to show overall averages by department compared to individual store department sales.

Is this even possible?

[v-shex-msft]

HI @5TimeGrandpa,

Based on feedback, current the url filters on power bi embedded seems broken, power bi team are working on it.
You can take a look at below link to know more about this issue.

Referenec link:

Power BI Embedded

Regards,

Xiaoxin Sheng

[TomMartens]

Hey,

you can create a Measure like this

Measure 2 = 
DIVIDE(
CALCULATE(
	SUM('FactWithDates'[Amount])
	,FILTER(ALL('Customer'[Customer])
		,'Customer'[Customer] IN VALUES('Customer'[Customer])
	)
)
,CALCULATE(
	SUM('FactWithDates'[Amount])
	,ALL('Customer'[Customer])	
)
)

You have to adjust the table and column names to your model.

An essential part of the measure above is the VALUES() function. Values captures the seleccted Customer (your Stores) in a table, this table is used to filter the expression in my example SUM(...[Amount]), this has to be replaced by a measure of your liking. In the first part of the division also FILTER is used, to make sure that just the selected Customers / Stores are considered

The measure also works if more than one Customer / Store is selected by a the slicer, thanks to VALUES(...)

Regards

Tom