cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power Participant

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

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?

3 ACCEPTED SOLUTIONS
Super User

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

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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]
)``````

Power Participant

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.

5 REPLIES 5
Power Participant

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.

Power Participant

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.

Super User

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

Super User

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

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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]
)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors