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
Aj24
Helper I
Helper I

Card Visual Sum by Distinct count of ID's

Hi everyone!

 

I'm looking to get the sum for distinct count of ID’s. Also, I would like it to sum only for distinct count of ID’s that are within the range of greater than or equal to 10% or less than or equal to -10%.

 

The percentages are calculated by this measure in my pbi file: Variance Thresholds = SUMX(VALUES('Table'[Month]), [Result])

 

 Eg of output:

 

ID

Last Date Updated

percentage

desired output

2

1/1/20

-11%

1

2

1/2/20

-20%

 

2

1/4/20

-25%

 

5

1/4/20

15%

1

7

1/7/21

10%

1

9

1/7/21

25%

1

 

desired result:

So the scorecard visual count result would be: 4

 

This is my power bi file: 

https://drive.google.com/drive/folders/160P9RckiEDHhMvOSijML4bFtEQnaJx9s?usp=sharing

 

I've tried 2 different simple test scripts (in the file) which didn't work at all: 

1.  test 5% var = IF([Variance Thresholds] >= 0.05, count([ID]), 0)

 

2. score card for project ID's variating by 10% = SUMX(VALUES('Table'[Month]), CALCULATE(DISTINCTCOUNT('Table'[Variance Thresholds])))

 

Thank you all so much in advance for sharing your expert advice!

 

2 REPLIES 2
amitchandak
Super User
Super User

@Aj24 , Try like

Sumx(summarize(Table, Table[ID], Table[Last Date], "_1", IF([Variance Thresholds] >= 0.05, 1, 0)),[_1])

thanks so much @amitchandak !! great idea!! Unfortunately, I tried this method and it didn't work... hoping for an alternative way of approaching this 🙂 

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.