Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Dear all,
I'm a PowerBI beginner and would appreciate your help.
I'm working on a report which has 2 related tables.
Table "Funnel", containing Customers column, Sales manager column, opportunities column for each customer and some other attributes of the opportunities.
Table "Portfolio" containing Sales manager and Customers (all the customers in the Sales managers' portfolio which are not necessarily all represented in the Funnel Table).
On Page 1 of the report there are serveral Slicer Visualizations for serveral attributes of the Opportunities of the Funnel table among which # of Customers per Sales Manager.
In the same Page 1 I would like to create a Column or Measure that shows the Portfolio (# of total customers) per Sales manager without being influenced by filters other than the Sales Manager filter and without being influenced by the relationship with the Funnel table given that the latter has less customers than the ones present in the Portfolio table.
Meaning that the Sales managers have open opportunities with only part of their overall Customer portfolio.
The final goal is to create a KPI measure by Sales manager, which should be dependent only on the Sales manager Slicer, representing the % of # of customers in the Funnel vs the Total # of customers in the Portfolio.
I read and tried different things but in the very end I find the Funnel==>Portfolio table relationship actually "slicing" per se the Portfolio # of customers limiting them to only the ones that appear also in the Funnel.
I'm available to further elaborate if needed.
Hi @Boris111 ,
Please try creating measures.
Portfolio Customers =
CALCULATE(
COUNT('Portfolio'[Customers]),
ALLEXCEPT('Portfolio', 'Portfolio'[Sales manager])
)
KPI =
DIVIDE(
COUNT('Funnel'[Customer]),
[Portfolio Customers],
0
)
For more information refer to
Using ALLEXCEPT versus ALL and VALUES - SQLBI
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Neeko,
thanks for your reply.
The DAX string that you propose works perfectly when used for a page in the report that uses data, and slicers, from the Portfolio table only and is used for a Visual in that page.
When it is being used for a Visual in the page whose slicers refer to the Funnel table (and page in the report), the Funnel table acts as a slicer and slices only those customers which are represented both in the Funnel and in the Portfolio thus leaving out (uncounted) those customers that are in the portfolio but haven't generated opportunities in the funnel.
The ALLEXCEPT function deals with the slicers but it doesn't prevent the Funnel table to "impose" its customer perimeter given that the two tables are Related and the direction of the relationship is from the Funnel table to the Portfolio table.
In all the things that I have read, at a certain point Bard.Google.com proposed the use of the "UNRELATED" function which sounded promising only that "UNRELATED" function doesn't exist in PBI.
If you still think a file is needed, I'll prepare a simpler one from scratch since I cannot share mine due to the commercial data presented inside.
Hi @Boris111 ,
You can delete the relationship between the two tables. Then try the following measure .If I misunderstood you I would like you to provide virtual input data and output data, preferably pbix files (please be careful to protect your private data).
Portfolio Customers =
CALCULATE(
COUNT('Portfolio'[Customers]),
ALL('Portfolio'), 'Portfolio'[Sales manager]=MAX('Portfolio'[Sales manager]))
)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |