Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kranthews
New Member

Assigning values based on actual Count vs Average Count

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

UserIDDateCount
101/01/2021100
201/01/2021500
301/01/2021200
401/01/202190
102/01/202140
202/01/2021200
302/01/2021300
402/01/2021250

 

Second Table

 

UserIDAverage Count
1200
2300
3100
450

 

Can someone please let me know how I do this in Power BI?

 

Many thanks,

Paul 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

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:

v-cazheng-msft_0-1610360645496.png

 

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.

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
Community Support

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:

v-cazheng-msft_0-1610360645496.png

 

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.

amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.