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,
I have an issue around the context of calculated measures which I am struggling to get my head around.
I have the following
1. Fact table (sales)
Customer ID
Date ID
ProductID
Leads Flag (0,1)
Sales Flag (0,1)
2. Date Dimension
DateID
[Various date variables]
3. Seller Table
SellerID
Sellers Name
4. Customer table
CustomerID
PhoneNumberFlag (contains 0s & 1s)
This allows me to create the following table by product and date range (i.e Weeknum):
Seller Count leads count Sale Percentage(measure)
Bob 10 8 80%
Dave 2 1 50
measure = Sum(sales)/sum(leads)
I need to know how many leads have a phone number . However when I do this I get the same answer across all rows of the table and am therefore igoring the filter context of the table
Seller Count leads count Sale Percentage(measure) sum of PhoneNumberFlag
Bob 10 8 80% 80000
Dave 2 1 50 80000
sum of PhoneNumberFlag = calculate(sum('Customer table'[PhoneNumberFlag]))
How do I ensure this measure is in the context of the Table in the report?
Many Thanks
Solved! Go to Solution.
Hi,
I have been playing and Cross directional filtering on the customer table seems to give me the right answer.
Thanks for your time
CALCULATE function needs to be used in combination with a FILTER or All/Allexcept/Allselected function.
Any chance to share the file with us or send us some sample data and the expected output?
e.g.
PhoneNumberFlag = calculate(sum('Customer table'[PhoneNumberFlag]), ALLEXCEPT('Seller Table', 'Seller Table'[Seller Name]))
I
Hi,
I have been playing and Cross directional filtering on the customer table seems to give me the right answer.
Thanks for your time
Hi,
I have been playing and Cross directional filtering on the customer table seems to give me the right answer.
Thanks for your time
I
Thanks.
I have recreated the problem and put the tables data in Excel - what is the best way to share it?
Richard
I have put some screen shots of the problem if that helps.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |