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.
I have a Sales table that looks like this:
SalesPersonID | State | SalesAmount |
235235 | CA | $5,000.00 |
123 | CA | $10,000.00 |
5156 | AZ | $80,000.00 |
23626 | AZ | $20,000.00 |
From Sales table, I used SUMMARIZE to create the following StateAvg table that gives me State Average:
State | SalesAmountPerPerson |
CA | $7,500.00 |
AZ | $50,000.00 |
I want to show the following Matrix just by using SalesPersonID slicer:
SalesPersonID | State | SalesAmount | SalesAmountStateAverage |
123 | CA | $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,
Solved! Go to Solution.
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:
or this
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
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:
or this
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
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |