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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

New Member

Need urgent help with DAX measure

Hi Friends 
I need an urgent help with DAX

The goal is to develop a measure for Category A which will sum A values by ID  for same dates and then average by dates for different dates

Please check the sample data for clarity and desired output

Sample Data

IDCategory Forecast DateCount


Desired Output - 

The goal is to develop a measure for Category A which will sum A values by ID  for same dates and then average by dates for different dates

Create a Matrix Visual with Forecast date as Columns
A50 + 100 + 15060 + 110 + 16070 + 120+ 170
 Sum the category by ID for the same dates  
Drill up to Quarter level
 Q3 2022  
A(( 50 + 100 + 150 ) + (60 + 110 + 160) + (70 + 120+ 170))  / 3  
 Average by  Date  for the lower level dates (NOT SUM)  



Similarly want to develop other measures for B and other categories

Can someone help with how to write measure for this please

thank you so much for the support

Community Support
Community Support

Hi @govinda108 ,


Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.


Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly


Best Regards,

Jianbo Li

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

Solution Sage
Solution Sage

// This measure takes the dates visible in the
// current context, for each such a date calculates
// the sum of Count, and then takes the average of
// these sums over all the visible dates. To get what you
// want all you need is just put your categories on rows...
// You have not specified, however, how this measure should
// behave if there are many categories visible in the 
// current context, so I've assumed you don't want to
// average these averages over the categories. Instead,
// the measure makes no distinction between
// the categories (they're treated as one) and only sums up
// within days. You can change this behavior easily but 
// it's up to you to decide what you want to see when such
// arrangement happens. Enjoy!

[Your Measure] =
        distinct( T[Forecast Date] ),
        calculate( sum( T[Count] ) )

Thanks @daXtreme 
I want to use the ID in the rows (22,23,24, etc.) for the matrix 
and date as columns

i intend to make separate measures for each category (A,B, C, etc) and intend to use that as values 



Why would you want to create a separate measure for each category if you can have one that gives you the same if you slice the data by category?


By the way, if you have one measure that works for all of them, then creating the other ones is easy. Just do this:


// Let X be any of your categories...
[Measure for Category X] =
  [General Measure],
  T[category] = "X"

// You can try to substitute my measure from above
// for [General Measure].


Super User
Super User




Sum by Cat/Date = CALCULATE(sum('Group'[Count]),ALLEXCEPT('Group','Group'[Category],'Group'[Forecast Date]))
Dis Date Count per Cat = CALCULATE(DISTINCTCOUNT('Group'[Forecast Date]),ALLEXCEPT('Group','Group'[Category]))
Step 3
Sum by Cat = CALCULATE(sum('Group'[Count]),ALLEXCEPT('Group','Group'[Category]))
Solution = DIVIDE([Sum by Cat],[Dis Date Count per Cat])



BONUS: Watch my video on ALL functions >

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users 

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors