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 two matrix pivot tables, these pivot tables are identical apart from a single column.
The values within the columns that differ have the same values, but in a different order.
So, each contains the following four values: Tier 1, Tier 2, Tier 3, Tier 4.
Column 1 is called LTA_Actual
Column 2 is called LTA_Plan
As the data matures the tier that the data has updates, which is why we have the two columns and two tables.
Ideally I'd like to have a page filter that automatically allows the user to filter to the tiers in both tables at once without having to manually set one or the other. From the user perspective they are aware we have plan and actual, but they just want to perform a single click.
I did try making a lookup table, but this did not seem to work.
Hi @Saarek ,
Without knowing the full details of your model is difficult to give the best answer.
Are the tiers based on the same data tables or different datasoureces?
Is the calculation made based on measures or a column value?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi There @MFelix , sorry, I should have been more specific. There is a single table which contains both of the columns and the Matrix is all based on the same single data table.
Best way to describe it is that we have a single financial column and then two of these "Tier" columns. In effect if I apply a filter for Tier 1 I want both Pivot Matrix tables to filter themselves to their own Tier 1.
The tiers are within the rows section and the other items within the Values area are identical.
Currently I have a filter for LTA_Actual and LTA_Plan, but as the same dataset if I filter on either of those the other pivot table is wrong.
What I want is a single filter that will set Pivot Table A to LTA_Actual and Pivot Table B to LTA_Plan. This works if I manually filter the pivot tables themselves, but I ideally want a page filter.
Hi @Saarek ,
If I understand corretly you have a similar setup to this one:
Cat | Value | Tier A | Tier B |
A | 100 | Tier 1 | Tier 3 |
B | 200 | Tier 2 | Tier 3 |
C | 150 | Tier 1 | Tier 4 |
D | 300 | Tier 3 | Tier 1 |
E | 100 | Tier 2 | Tier2 |
Now you want to select 1 tier on a slicer or similar and both visualizations show that Tier.
I would do the following:
Tier
Tier 1 |
Tier 2 |
Tier 3 |
Tier 4 |
Tier A =
CALCULATE (
SUM ( Data[Value] );
FILTER ( ALLSELECTED ( Data[Tier A] ); Data[Tier A] IN VALUES ( Tiers[Tier] ) )
)
Tier B =
CALCULATE (
SUM ( Data[Value] );
FILTER ( ALLSELECTED ( Data[Tier B] ); Data[Tier B] IN VALUES ( Tiers[Tier] ) )
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix , thank you for your help so far! I've tried opening your .pbix file, unfortunately my desktop version is older than yours and as I'm on a corporate computer I cannot update.
Below is a screen shot with dummy data of what the view currently looks like.
Your code looks like it would allow me to work around and make this work with a single filter/slicer, but I am getting the following error:
**** EDIT****
Code now works when I replace the semi colon with commas.
So, your solution works, but seemingly only if I can move all my sums to the new table. But there are lots of columns people want to filter by and I can't move them all to the new table.
Hi @Saarek ,
The error you are getting is related with regional settings you need to change the dot comma ";" by "," and it will work fine has you refer.
Not understanding the part below:
So, your solution works, but seemingly only if I can move all my sums to the new table. But there are lots of columns people want to filter by and I can't move them all to the new table.
The new table does not need to have any other data than the TIERS using the measures you are maknig the relation between the tiers and the calculations, what you can do in order not to have several measures that need to be redone based on the tiers you can use calculated groups.
But can you show a mockup of your data tables? That way I can adjust the process if needed,
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |