Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
Hi @rpinxt - create a new measures for unique Lines and Hours as below:
For New measure Hours:
Now let's calculate another measure for the totalUpH based on the unique sums.
TotalUpH = DIVIDE([UniqueLines], [UniqueHours])
Hope it works
Proud to be a 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]
)
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 :
But this did not change anything.
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.
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 :
But this did not change anything.
Those results look odd for sure. Is the [User name] column in your table visual coming from a different data table?
Hi @rpinxt - create a new measures for unique Lines and Hours as below:
For New measure Hours:
Now let's calculate another measure for the totalUpH based on the unique sums.
TotalUpH = DIVIDE([UniqueLines], [UniqueHours])
Hope it works
Proud to be a 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]
)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
68 | |
40 | |
29 | |
26 |
User | Count |
---|---|
88 | |
49 | |
45 | |
38 | |
37 |