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 what seems like a simple problem, but I have so far failed to find an existing solution - apoligies if this exists somewhere already - I cant find it!
I need to display historical revenues from several clients. This is no problem on its own, but these clients had monthly minimum billing rates independent from their actual product usage over the time period.
Here is the sample minimum table:
Date | Customer | Monthly Minimum |
1/1/2021 | Customer A | 200 |
1/1/2021 | Customer B | 500 |
2/1/2021 | Customer A | 200 |
2/1/2021 | Customer B | 500 |
3/1/2021 | Customer A | 200 |
3/1/2021 | Customer B | 500 |
4/1/2021 | Customer A | 200 |
4/1/2021 | Customer B | 500 |
5/1/2021 | Customer A | 200 |
5/1/2021 | Customer B | 500 |
6/1/2021 | Customer A | 200 |
6/1/2021 | Customer B | 500 |
7/1/2021 | Customer A | 200 |
7/1/2021 | Customer B | 500 |
8/1/2021 | Customer A | 200 |
8/1/2021 | Customer B | 500 |
9/1/2021 | Customer A | 200 |
9/1/2021 | Customer B | 500 |
10/1/2021 | Customer A | 200 |
11/1/2021 | Customer A | 200 |
12/1/2021 | Customer A | 200 |
In addition, I have the actual usage data in the following format:
Date | Customer | Product | Rate | Volume | Revenue |
1/1/2021 | Customer A | Product 1 | 0.05 | 733 | 36.65 |
1/1/2021 | Customer A | Product 2 | 0.08 | 491 | 39.28 |
1/1/2021 | Customer A | Product 3 | 0.02 | 724 | 14.48 |
2/1/2021 | Customer A | Product 1 | 0.05 | 556 | 27.8 |
2/1/2021 | Customer A | Product 2 | 0.08 | 495 | 39.6 |
2/1/2021 | Customer A | Product 3 | 0.02 | 713 | 14.26 |
3/1/2021 | Customer A | Product 1 | 0.05 | 322 | 16.1 |
3/1/2021 | Customer A | Product 2 | 0.08 | 632 | 50.56 |
3/1/2021 | Customer A | Product 3 | 0.02 | 487 | 9.74 |
4/1/2021 | Customer A | Product 1 | 0.05 | 239 | 11.95 |
sample | data | continues.. |
I have them joined to a traditional dimdate table like so:
Then I am able to make a visual like this:
which is great - monthly minimum shows up exactly as intended...BUT when i filter by customer, the monthly minimum does not filter with it - for example, here is the same visual for "Customer B" only:
I have tried adding additional relationships but nothing seems to work...is there a best practice for this type of situation?
Solved! Go to Solution.
Hi @mangoose736
What I've done is create a new table to pull in only the customer value from the smpldata table
smpldatarelationship = ALL(smpldata[Customer])
From there I created a relationship between the revmin and newly created table.
Replace your slicer value with the new table 'Customer' column and you will now be able to filter between the different customers with your graph responding correctly.
Hi @mangoose736
What I've done is create a new table to pull in only the customer value from the smpldata table
smpldatarelationship = ALL(smpldata[Customer])
From there I created a relationship between the revmin and newly created table.
Replace your slicer value with the new table 'Customer' column and you will now be able to filter between the different customers with your graph responding correctly.
This is the solution - thank you so much!
Hi @mangoose736
Have you tried setting the cross filter direction to Both instead of Single? This may fix your issue.
Unfortunately no, I get the same results either way - both or single cross filtering.
I have saved this sample file to onedrive here: https://1drv.ms/u/s!AoFM9U3goeChkbxIF-TWqisTT8iP9Q?e=mdgjJe
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |