DAX how to get total within a group

[clayduvall]

For some reason I'm struggling with what I think shoudl be a very simple calculation and I don't know why.  I'm simply trying to get the total fo a measure within a group.  I tried this.  It works when I don't add the FILTER, but it includes all of the Providers in the table even if they don't have values for Combined - Count.  And when I add the FILTER which copilot suggested, it returns the same value at Combined Count for all rows.  Any ideas on what I'm missing here?

Combined - Total All Providers:=
CALCULATE(
'Combined Service'[Combined - Count],
ALLEXCEPT('Provider', 'Provider'[Full Name]),
FILTER('Combined Service', 'Combined Service'[Combined - Count] > 0)
)

[clayduvall · Accepted solution]

I finally figured it out after many back and forths with copilot.  This will work.

MS - Total services:=
VAR ValidRows =
FILTER(
ALL('Combined Type'),
NOT(ISBLANK([MS - Services])) && [MS - Services] <> 0
)
VAR TotalServices =
CALCULATE(
[MS - Services],
ValidRows
)
RETURN
IF([MS - Services] > 0, TotalServices, BLANK())

[v-karpurapud]

Hi @clayduvall 

Could you please confirm if your query have been resolved the solution provided by @Jihwan_Kim ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[clayduvall]

Unfortunately, I didn't get a solution for this.  But I'm working on a workaround in SQL to solve it.  I appreciate the responses.

[v-karpurapud]

Hi @clayduvall 

Thank you for sharing your workaround using SQL. However, the solution provided by Prasanna is currently more helpful for Power BI. If you have identified a solution is helpful, please share it with the community to assist others facing similar issues.If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank You!

[clayduvall]

I didn't mean to imply that I was just using SQL.  I just need this measure as part of the DAX model so that it can be used when connecting to the model from Excel, not just PowerBI.  The visual calculations are a very nice feature.  It's just not quite what I need. I'm working on that by precalculating some of these in the SQL first.  But I'm still working through it.   When I have a complete solution, I'll share it.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

One of ways is to use Visual Calcualtion feature in Power BI desktop.

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Please check the below picture and the attached pbix file.

[clayduvall]

Thank you.  This will help. I was hoping to add it to the model since there are some Excel users using this model.  But at least I have an answer within power bi.

[lbendlin]

 I'm simply trying to get the total fo a measure within a group.  

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.