A way to calculate / sum the unique values in a table

[rpinxt]

Consider this data sample :

User	Lines	Hours	UpH
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Jan	147	2.1	70
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Piet	200	2.6	77
Total	347	4.7	74

UpH is Lines / Hours

On a line level this is straight forward.

But on a total level it should only sum the unique values.

Summing unique values would also work on a line level because there is only 1 value.

But can it be done sum unique?

[rajendraongole1 · Accepted solution]

Hi @rpinxt - create a new measures for unique Lines and Hours as below:

UniqueLines =

SUMX(

    SUMMARIZE(

        'lhp',

        'lhp'[User],

        "UniqueLines", MAX('lhp'[Lines])

    ),

    [UniqueLines]

)

For New measure Hours:

UniqueHours =

SUMX(

    SUMMARIZE(

        'LHP',

        'LHP'[User],

        "UniqueHours", MAX('LHP'[Hours])

    ),

    [UniqueHours]

)

Now let's calculate another measure for the totalUpH based on the unique sums.

TotalUpH = DIVIDE([UniqueLines], [UniqueHours])

Hope it works

[AlexisOlson · Accepted solution]

Note that this assumes only one unique set of values for each user.

If this is not the case, try summarizing over the appropriate level of granularity. For example, this groups over users and lines:

UniqueLines =
SUMX (
    SUMMARIZE (
        'lhp',
        'lhp'[User],
        'lhp'[Lines]
    ),
    'lhp'[Lines]
)

[rpinxt]

Thanks again @AlexisOlson and @rajendraongole1 

Your solutions work but not entirely how we had our data modeled.

We made changes now and found a solutions.

Thanks for your help.

[rpinxt · Accepted solution]

Thanks for this @rajendraongole1 and @AlexisOlson I appreciate it.

However with the first method I did not get unique values per user :

So guessing it was because of what Alexis said, howver that method also did not give me unique lines per user.

181460 is the total number of rows in the dataset.

Used this code :

UniqueLines =

SUMX(

    SUMMARIZE(

        'Dom/Can q3/4',

        'Dom/Can q3/4'[User name],

        'Dom/Can q3/4'[Total lines]

    ),

    'Dom/Can q3/4'[Total lines]

)

I also was thinking if we want to make date part of the logic to get a unique number per dag with :

But this did not change anything.

[AlexisOlson]

Those results look odd for sure. Is the [User name] column in your table visual coming from a different data table?

[rajendraongole1 · Accepted solution]

Hi @rpinxt - create a new measures for unique Lines and Hours as below:

UniqueLines =

SUMX(

    SUMMARIZE(

        'lhp',

        'lhp'[User],

        "UniqueLines", MAX('lhp'[Lines])

    ),

    [UniqueLines]

)

For New measure Hours:

UniqueHours =

SUMX(

    SUMMARIZE(

        'LHP',

        'LHP'[User],

        "UniqueHours", MAX('LHP'[Hours])

    ),

    [UniqueHours]

)

Now let's calculate another measure for the totalUpH based on the unique sums.

TotalUpH = DIVIDE([UniqueLines], [UniqueHours])

Hope it works

[AlexisOlson · Accepted solution]

Note that this assumes only one unique set of values for each user.

If this is not the case, try summarizing over the appropriate level of granularity. For example, this groups over users and lines:

UniqueLines =
SUMX (
    SUMMARIZE (
        'lhp',
        'lhp'[User],
        'lhp'[Lines]
    ),
    'lhp'[Lines]
)