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
sedric1
Helper III
Helper III

Table totals are not counting as expected in visual

Hi I have a single table full of user IDs. Some of them user IDs are duplicated, some have blank values and some have #N/A values (which i was hoping to filter down to a count of distinct user IDs, categorized by the type of user. 

 

The categorized column for users displays super users vs end users using a new column, and tried to categorize the counts and show the total count (formula below)

User Type = if ( ISBLANK(Users[TMS ID])=FALSE() && Users[TMS ID] <> "#N/A", IF (
ISBLANK('Users'[SU TMS Program])=TRUE(),
"End User",
"Super User"
), "")
 
sedric1_0-1600702938351.png
 

First I tried this measure but total counts still werent correct:

User Count = CALCULATE(DISTINCTCOUNTNOBLANK('URA'[TMS ID]),'URA'[TMS ID]<>"#N/A", 'URA'[TMS ID]<>blank() )
 
After reading some thoughts on other posts, I realized that measures and totals have issues calculating so then I tried just using the power bi options to count distinct + filter out the value from the visuals, but it doesnt seem to apply to the totals.
 

 

 

Also here is a sample of the data:

TMS IDUser TypeSU TMS Program
DLSDJFSVNWEN.KLSDJFSLLY1112Super UserTraining Curriculum Assigned
DLSDJFSLLNWENNT.LNWESldk219Super UserTraining Curriculum Assigned
19922411Super UserTraining Curriculum Assigned
JNTBLSDJFSS.BRldkNDNWE2912Super UserTraining Curriculum Assigned
CRldkNWEGMNWELLSDJFS.SUSldkN27292Super UserTraining Curriculum Assigned
HUGHLSDJFSS.LNWENldk12921Super UserTraining Curriculum Assigned
 Super UserTraining Curriculum Assigned
HNTLLNTWldkY.LSDJFSLNWEZldkBLSDJFSTH21Super UserTraining Curriculum Assigned
TMS IDUser TypeSU TMS Program
 End User 
#N/AEnd User 
#N/AEnd User 
#N/AEnd User 
11127End User 
11127End User 
1179222End User 

 

 

1 ACCEPTED SOLUTION
VijayP
Super User
Super User

@sedric1 

 

SWITCH(TRUE()

NOT(ISBLANK(TMSID)) || TMSID<>"#N/A"||ISBLANK(TMSPROGRAM),"End USer","Super USer"

Try this function and share your kudoes




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


View solution in original post

5 REPLIES 5
VijayP
Super User
Super User

@sedric1 

 

SWITCH(TRUE()

NOT(ISBLANK(TMSID)) || TMSID<>"#N/A"||ISBLANK(TMSPROGRAM),"End USer","Super USer"

Try this function and share your kudoes




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@VijayP  Thanks for the quick reply. I am still learning and therefore not sure where I would enter a function like that. Can you guide to where functions can be defined in power bi? 

@sedric1 

Create a Calculated Column. Or Create a measure and add that measure in to the Table visual

Please sshare your kudoes




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@VijayP  Thanks a lot! it appears to be working . I will mark this as resolved but 1 thing I am unclear on is- why this works in your custom formula, yet the way I did a custom column did not work originally?

 

sedric1_0-1600707973051.png

 

Either way, thanks again!

 

@sedric1 

Thanks , Pleasae mark this as Solution !




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


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.