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 all,
I'm very new to Power BI and struggling a bit
For each day, I want to assign values and then sum those values based on an actual count value vs an average count value for each user.
For example, if a user's Count value is >= 50% of their Average Count then I want to give a value of 50, If the user's Count value >= 75% of their Average Count then I want to give an additional value of 75 and if the user's Count value is >= 100% then I want to give an additional value of 100.
Then for every 10% greater than their 100% average value, I want to give a value of 25.
I have two tables
First Table
UserID | Date | Count |
1 | 01/01/2021 | 100 |
2 | 01/01/2021 | 500 |
3 | 01/01/2021 | 200 |
4 | 01/01/2021 | 90 |
1 | 02/01/2021 | 40 |
2 | 02/01/2021 | 200 |
3 | 02/01/2021 | 300 |
4 | 02/01/2021 | 250 |
Second Table
UserID | Average Count |
1 | 200 |
2 | 300 |
3 | 100 |
4 | 50 |
Can someone please let me know how I do this in Power BI?
Many thanks,
Paul
Solved! Go to Solution.
Hi @kranthews ,
For the Count Value>=100% Average Count, for example, the Count Value is 100 and the Average Count is 40.
Is the calculation logic like 100+((100-40)/(100-40)*10%)*25?
If the logic is right, you can create a Measure to get the result you want. If not, please correct my calculation logic.
Assign Values =
var _t2acount=CALCULATE(MAX('Second Table'[Average Count]),FILTER('Second Table','Second Table'[UserID]=MAX(FirstTable[UserID])))
var _t1count=SELECTEDVALUE(FirstTable[Count])
return
IF(
_t1count>=_t2acount,
100+DIVIDE(_t2acount-_t1count,(_t2acount-_t1count)*0.1)*25,
IF(
_t1count>=0.75*_t2acount,
75,
IF(
_t1count>=0.5*_t2acount,
50,
0
)
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @kranthews ,
For the Count Value>=100% Average Count, for example, the Count Value is 100 and the Average Count is 40.
Is the calculation logic like 100+((100-40)/(100-40)*10%)*25?
If the logic is right, you can create a Measure to get the result you want. If not, please correct my calculation logic.
Assign Values =
var _t2acount=CALCULATE(MAX('Second Table'[Average Count]),FILTER('Second Table','Second Table'[UserID]=MAX(FirstTable[UserID])))
var _t1count=SELECTEDVALUE(FirstTable[Count])
return
IF(
_t1count>=_t2acount,
100+DIVIDE(_t2acount-_t1count,(_t2acount-_t1count)*0.1)*25,
IF(
_t1count>=0.75*_t2acount,
75,
IF(
_t1count>=0.5*_t2acount,
50,
0
)
)
)
The result looks like this:
Here is the sample.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@kranthews , user 4 is 90 + 250, who his score is 50 and why user 1 as 200 , please provide details on that
Hi Amitchandak, the Average Count in Table 2 is calculated based on 90 days of previous data. I have worked out each user's Average Count, but now want to compare this average count against this year's data from Table 1.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |