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.
Hi everyone!
I`d like to create some basic benchmarking in PIB and for this reason want to compare data from different companies with each other.
In a first, I aimt to select focal company with slicer 1. Then, I`d like to select peer companies for benchmarking by means of slicer 2. (Both slicers use the same company column in my dataset.) Based on these selections, a visual should afterwards ideally display data from the focal companies as well as from its selected peers.
Below, you`ll find a quick draft of what I`m looking for. How is it possible to include the company from slicer 1 in the visual?
Thank you very much in advance for any help! 🙂
Solved! Go to Solution.
Thanks for your suggestion, very helpful! I`ve tried to use it with the structure of my data and think we`re already very close to what I`m looking for. Please find attached my page based on your solution.
There are basically two things which I still need:
- How is it possible to select multiple peers at once, say A, B, and C and compare it with our focal company D?
- Is there an "easy" way to exclude the company selected as focal company in the pool of peers available for comparison? E.g. I first select company D as our focal company and then only A, B, and C should be available for selection.
Thanks a lot in advance!
Hi @MSC
Try measure as:
Measure =
IF(
MAX(Data[Company]) IN ALLSELECTED(Company_peers[Peers]) || MAX(Data[Company]) IN ALLSELECTED(Company_focal[Focal]),
1,
BLANK()
)
For the second requirement you describe, dynamic filtering of two dimension tables at the same time is not supported.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @MSC,
Create two dimension table as:
Company_dim1 =
VALUES('Table'[Company])
Company_dim2 =
VALUES('Table'[Company])
Create measures as:
Dim1 =
CALCULATE(
MAX('Table'[Sales]),
FILTER(
'Table',
'Table'[Company]=ALLSELECTED(Company_dim1[Company_dim1])
)
)
Dim2 =
CALCULATE(
MAX('Table'[Sales]),
FILTER(
'Table',
'Table'[Company]=ALLSELECTED(Company_dim2[Company_dim2])
)
)
Here is the output:
The demo is attached, please try it.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Thanks for your suggestion, very helpful! I`ve tried to use it with the structure of my data and think we`re already very close to what I`m looking for. Please find attached my page based on your solution.
There are basically two things which I still need:
- How is it possible to select multiple peers at once, say A, B, and C and compare it with our focal company D?
- Is there an "easy" way to exclude the company selected as focal company in the pool of peers available for comparison? E.g. I first select company D as our focal company and then only A, B, and C should be available for selection.
Thanks a lot in advance!
Hi @MSC
Try measure as:
Measure =
IF(
MAX(Data[Company]) IN ALLSELECTED(Company_peers[Peers]) || MAX(Data[Company]) IN ALLSELECTED(Company_focal[Focal]),
1,
BLANK()
)
For the second requirement you describe, dynamic filtering of two dimension tables at the same time is not supported.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
@v-xulin-mstf
Hi there Link and all community members!
In the meantime, I have come across a follow-up question with regard to your/Link`s solution (which works perfectly fine!), I could not find an answer for.
Please find attached the corresponding
pbix file and
excel file (if dataset is needed).
The demo file has the same underlying model as my original pbix file.
Basically, I would like to sync the company slicer on the "current report" page with that of the (focal) company slicer on the "new report" page.
This is because at the moment I have to select the focal company on my benchmarking page (which here is called "suggestion by link" because it follows your/his solution) manually.
Example: I select Danone SA on "current report", then the first slicer on "new report" should also display Danone SA and I should be able to add some peers thereafter.
Is there a possibility with which this can be solved?
Thank you very much in advance for any help!
@v-xulin-mstfSorry for the delayed response, Link.
Thank you very much for your help - very appreciated it!
It`s really great to see that there are a lot of people in the community always eager to help with their skills and experience.
@MSC , Are you trying to use two slicers on the same column. You need one or two independent table .
I have examples for date
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Also this one
Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
@amitchandakThank you for the quick response! I`ve to admit that I still don`t know how to approach my problem because in the videos you only use one slicer, right? How does the independent table help with two slicers?
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |