SUM(X) and RoundUp -- maybe??

[sharpedogs]

Hi, 

I've been trying all kinds of tricks but I can't seem to get the correct output i need..

I'm trying to fingure out how many licenses my team requires for a product. 

The license rule is that a single user can deply the Software on only 2 devices, if a user deploys it on more devices they need to purchase additional licesnes

1 or 2 devices = 1 licesne

3 or 4 deivces = 2 licesnes

5 or 6 devices = 3 licenses 

I took the devices and did a Distintcount[DeviceName], then i put it in a matrix next to the user name. This tells me how many unique devices each user has. Then i created a Licesne Requirement =Distintcount[DeviceName]/2.  The output is below. 

The issue with the output is this, i need to round the Licenses Required up to the next whole number. For example, user Sam Licenses Required should read 3 not 2.5. 

The second issue I have is that when i use a DAX with round or SUMX i can never ger the total to add up correctly. 

User	Distinct Devices	Licenses Required	Wanted outcome
Sam	5	2.5	3
Chad	3	1.5	2
Dave	2	1	1
Jeff	1	0.5	1
Linda	1	0.5	1
		6	8

[mahoneypat · Accepted solution]

This expression seems to work with your example data.  Replace Licenses with your actual table name.

Licenses Needed =
SUMX (
VALUES ( Licenses[User] ),
CALCULATE ( ROUNDUP ( DISTINCTCOUNT ( Licenses[DeviceID] ) / 2, 0 ) )
)

or using your existing measure

Licenses Needed =
SUMX (
VALUES ( Licenses[User] ),
ROUNDUP ( [Distinct Devices] / 2, 0 ) )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[amitchandak]

@sharpedogs ,

you can use round([Licenses Required],0)

Check also these two functions

https://docs.microsoft.com/en-us/dax/rounddown-function-dax

https://docs.microsoft.com/en-us/dax/roundup-function-dax

[sharpedogs]

I've used the round function as you noted and it works only to correctly round up the number to a whole number. There is also somehting going on in the backgroud with the Matrix as the Total does not reflect the actual number in the column. That' when i tired a sum X, but that didn't work either?