Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
novice12
New Member

Need to calculate distinct counts based on a measure as a filter DAX

Hi first time posting but have been struggling with this for too long.

 

I have a large table with the following 

 

ID | Status

1     Above Budget

2     Below Budget

3     Above Budget

4     Above Budget

 

The status column is a measure based off the delta between two other columns. It needs to be a measure, as I need this Status calculated dynamically based on the filter/slicer selections on the page.

 

If I select a table, the measure shows up correctly for each record.

But what I need is a pie chart off this table that would show 75% Above Budget and 25% Below Budget

 

Since I cannot use measure in the legend of the chart I have tried to create two separate measures, one for Above Budget and another for Below Budget. However, the result I am getting is NOT accurate, I am seeing 4 show up for both Above Budget and Below Budget, likely due to the various granularity of my data.

I have tried various iterations of the below DAX, including sum the forecast and budget but result is always incorrect

Below Budget= calculate(DISTINCTCOUNT([ID]),FILTER(Report,[Forecast]- [Budget]<0))


Any ideas on how to get the correct values to show up?

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @novice12 
You can try these 3 measures :

Frcst_Bud_diff =
SUMX('Table','Table'[Forecast]-'Table'[Budget])

Above_budget =
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table',[Frcst_Bud_diff]>0))
Below_budget =
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table',[Frcst_Bud_diff]<0))
Result:
Ritaf1983_0-1737746859311.png

The PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

1 REPLY 1
Ritaf1983
Super User
Super User

Hi @novice12 
You can try these 3 measures :

Frcst_Bud_diff =
SUMX('Table','Table'[Forecast]-'Table'[Budget])

Above_budget =
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table',[Frcst_Bud_diff]>0))
Below_budget =
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table',[Frcst_Bud_diff]<0))
Result:
Ritaf1983_0-1737746859311.png

The PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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