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 have been stuck in a issue where I am receiving incorrect values based on my formula(I know I might have written incorrect formula)
I have tow tables as below
Table 1: Raw - Which gives information about user and Director for each user
Table 2: Information about user, kind of event user had and data of event
Overall under Raw table, I have 15 users, out of which 9 are under Director 1 and 6 are under Director 2
I am trying to create below timeline slicer to check % of users who had an event during a specific timeframe and I am using a slicer to filter based on the director as shown below and I am using below DAX formula to calculate the % of users with events
% of user with actions = DIVIDE(CALCULATE(DISTINCTCOUNT(Test[User])),CALCULATE(DISTINCTCOUNT(Raw[User]),ALL(Test)))
To issue here I am facing is, When I choose, Dir 1 in slicer and a specific timeframe, There are 6 users who had events under Dir 1
The percentage value I am expecting is 6/9(66.6%) where 9 is the total number of users under Dir 1 however, I am getting 40%(6/15) the formula is taking all users in denominator instead of users only under Dir 1.
I know there is some issues with my formula and checked few resources with no luck. Anyone who had this issue here or any possible solution for the problem please let me know the approach.
Thanks in advance.
Cheers,
Praj
Solved! Go to Solution.
Hello Praj,
Can you try the below? What I did was instead of unfiltering the whole Test table, I'm unfiltering only the User field. (I also removed what I think is an unnecessary CALCULATE and moved the denominator into a variable for ease of reading.)
% of user with actions =
VAR AllUsers =
CALCULATE (
DISTINCTCOUNT ( Raw[User] ),
ALL ( Test[User] )
)
RETURN
DIVIDE (
DISTINCTCOUNT ( Test[User] ),
AllUsers
)
I've made some assumptions about the data model and your visual so if the above doesn't work for you, please provide a pbix. It would help me tremendously. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hello Praj,
Can you try the below? What I did was instead of unfiltering the whole Test table, I'm unfiltering only the User field. (I also removed what I think is an unnecessary CALCULATE and moved the denominator into a variable for ease of reading.)
% of user with actions =
VAR AllUsers =
CALCULATE (
DISTINCTCOUNT ( Raw[User] ),
ALL ( Test[User] )
)
RETURN
DIVIDE (
DISTINCTCOUNT ( Test[User] ),
AllUsers
)
I've made some assumptions about the data model and your visual so if the above doesn't work for you, please provide a pbix. It would help me tremendously. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
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 |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |