cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
govinda108
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
22A10/1/202250
22A11/1/202260
22A12/1/202270
23A10/1/2022100
23A11/1/2022110
23A12/1/2022120
24A10/1/2022150
24A11/1/2022160
24A12/1/2022170
22B10/1/202280
22B11/1/202290
22B12/1/2022100
23B10/1/2022300
23B11/1/2022350
23B12/1/2022400
24B10/1/2022410
24B11/1/2022420
24B12/1/2022430

 

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/202211/1/202212/1/2022
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

5 REPLIES 5
v-jianboli-msft
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.

daXtreme
Super User
Super User

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

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 

@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
Super User
Super User

@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])
ribisht17_0-1659844732763.png

 

 

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 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors