Need urgent help with DAX measure

[govinda108]

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

[v-jianboli-msft]

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.

[daXtreme]

// 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] =
calculate(
    averagex(
        distinct( T[Forecast Date] ),
        calculate( sum( T[Count] ) )
    )
)

[govinda108]

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 

[daXtreme]

@govinda108 

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].

[ribisht17]

@govinda108 

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