cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pucities Visitor
Visitor

Pick a Value From Slicer to Update matrix From Multiple Tables

I have a Sales table that looks like this:

SalesPersonIDStateSalesAmount
235235CA$5,000.00
123CA$10,000.00
5156AZ$80,000.00
23626AZ$20,000.00

 

From Sales table, I used SUMMARIZE to create the following StateAvg table that gives me State Average:

StateSalesAmountPerPerson
CA$7,500.00
AZ$50,000.00

 

I want to show the following Matrix just by using SalesPersonID slicer:

SalesPersonIDStateSalesAmountSalesAmountStateAverage
123CA$10,000.00$7,500.00

 

I built a bridge table for State. SalesPersonID is unique for each state. 

 

When I put 2 slicers: 1 for state and 1 for SalesPersonID, and select State=CA and SalesPersonID=123 in each slicer, I get what I want (3rd table above). 

However, when I just select SalesPersonID = 123 in a slicer, I get average for all states. I cannot create a relationship between Sales and StateAvg table directly because StateAvg table doesn't have SalesPersonID. 

 

How do I make it so that I can just select a SalesPersonID using Slicer to get the state average?

 

Thanks,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Pick a Value From Slicer to Update matrix From Multiple Tables

Hey @pucities ,

 

I don't think it's necessary to use a table (SUMMARIZE), I created this measure:

Average Sales Amount = 
AVERAGEX(
    'Table1'
    , var _State = 'Table1'[State]
    return
    CALCULATE(
        AVERAGE('Table1'[SalesAmount])
        , ALL('Table1')
        , 'Table1'[State] = _State
    )
)

This allows to create a report like this:

image.png

or this

image.png

Depending on your data model (star-schema or a one-table solution) the DAX has to be adjusted.

 

Hopefully this is what your are looking for.

 

Regards,

Tom

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Super User
Super User

Re: Pick a Value From Slicer to Update matrix From Multiple Tables

Hey @pucities ,

 

I don't think it's necessary to use a table (SUMMARIZE), I created this measure:

Average Sales Amount = 
AVERAGEX(
    'Table1'
    , var _State = 'Table1'[State]
    return
    CALCULATE(
        AVERAGE('Table1'[SalesAmount])
        , ALL('Table1')
        , 'Table1'[State] = _State
    )
)

This allows to create a report like this:

image.png

or this

image.png

Depending on your data model (star-schema or a one-table solution) the DAX has to be adjusted.

 

Hopefully this is what your are looking for.

 

Regards,

Tom

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 414 members 3,758 guests
Please welcome our newest community members: