cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 ID Category Forecast Date Count 22 A 10/1/2022 50 22 A 11/1/2022 60 22 A 12/1/2022 70 23 A 10/1/2022 100 23 A 11/1/2022 110 23 A 12/1/2022 120 24 A 10/1/2022 150 24 A 11/1/2022 160 24 A 12/1/2022 170 22 B 10/1/2022 80 22 B 11/1/2022 90 22 B 12/1/2022 100 23 B 10/1/2022 300 23 B 11/1/2022 350 23 B 12/1/2022 400 24 B 10/1/2022 410 24 B 11/1/2022 420 24 B 12/1/2022 430

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 10/1/2022 11/1/2022 12/1/2022 A 50 + 100 + 150 60 + 110 + 160 70 + 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

5 REPLIES 5
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

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
``````// 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!

calculate(
averagex(
distinct( T[Forecast Date] ),
calculate( sum( T[Count] ) )
)
)``````
New Member

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

Solution Sage

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] =
calculate(
[General Measure],
T[category] = "X"
)

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

Super User

Step1

Sum by Cat/Date = CALCULATE(sum('Group'[Count]),ALLEXCEPT('Group','Group'[Category],'Group'[Forecast Date]))

Step2
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]))

Step4
Solution = DIVIDE([Sum by Cat],[Dis Date Count per Cat])

BONUS: Watch my video on ALL functions > https://youtu.be/fEIaJigVfFg

Thanks,
Ritesh
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