cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adithyanaini
Post Partisan
Post Partisan

Need help in Dax Formula (SUM & group BY)

I have the sample data like below

 

DW_ID     DATE     Head    

1             05/30            3

1            05/29           4

1            05/28            6

2           05/24            11

3           05/30             14

3         05/22               10

 

In detail : I need the sum(Head) group by Dw_ID for the latest date of the DW_ID . how can i achieve this in DAx?

These dates will be applied from the filter slicer . If i select for example  date :05/30 : Head =3+14 =17 . If i select the range of dates from 05/22 to 05/30 Head =14+11+3 =28 . How can i achieve this from Dax measure .?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Assuming you have a date table called DimDate which is related to your fact table. Using the dates from the DimDate table you can write the following measure:

Measure 2 = 
Var __LastDateTable = 
SUMMARIZE( 
	Table2, 
	Table2[DW_ID] , 
	"LastDate", LASTDATE( Table2[Date])
)

Var __AddColumn =
ADDCOLUMNS(
	__LastDateTable,
	"Value at Last Date", 
	CALCULATE( 
		SUM ( Table2[Head] ),
		FILTER( 
			Table2,
			[LastDate] = Table2[Date]
			&& Table2[DW_ID] = EARLIER( Table2[DW_ID])
			)
)
)
RETURN
 CALCULATE(
     SUMX( __AddColumn, [Value at Last Date] )
 )

Range of Dates, Sum Max.png

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Assuming you have a date table called DimDate which is related to your fact table. Using the dates from the DimDate table you can write the following measure:

Measure 2 = 
Var __LastDateTable = 
SUMMARIZE( 
	Table2, 
	Table2[DW_ID] , 
	"LastDate", LASTDATE( Table2[Date])
)

Var __AddColumn =
ADDCOLUMNS(
	__LastDateTable,
	"Value at Last Date", 
	CALCULATE( 
		SUM ( Table2[Head] ),
		FILTER( 
			Table2,
			[LastDate] = Table2[Date]
			&& Table2[DW_ID] = EARLIER( Table2[DW_ID])
			)
)
)
RETURN
 CALCULATE(
     SUMX( __AddColumn, [Value at Last Date] )
 )

Range of Dates, Sum Max.png

View solution in original post

@Anonymous : Can you help with difference one please below is description:

 

i have the simple scnerio as below .

 

dw_id   diagname                                   dead                 date

1           heartfailure                                 2                     06/07/2019

2              kidney problem                     3                     04/07/2019

3             asthama                                       4                     05/07/19

i have like 100 category of dignosisname and i have used the bar chart to show only 10 deads during that period . where i have the date as slicer and dw_id in the filterpane . i need to have the measure of dead % when i categorize by diganosisname  = dead of that category/total dead . But when i strict the top 10 it is stricting the total count for that tile and % is showing the wrong value . For example i have total dead for the time period of jan 2019 is 400 and top 10 dead is limiting to 300 count . Value is being calculated on 300 instead of 400 . Is there any way i can ignore the top N filter for calculation ?

 

currently i am using measure like below :

 

dead % by diagnname = divide(sum(dead),calculate(sum(head),allexpect(date,dw_id))

 

 

Anonymous
Not applicable

can you upload some sample data?

@Anonymous 

hi nick i have the data like below and i have the total dead of 36 with below records and i have the bar chart in below where i am showing the top 3 records in barchart and the barchart is showing the formula of top n with formula and PBX in below thread .  Please refer below thread and want i am looking for in below thread . I hope you respond positively :

 

https://community.powerbi.com/t5/Desktop/ignore-the-top-N-filter-for-calculation-of-percentage-value/m-p/710322/highlight/false#M342961

 

. My user is saying he need the formula in below way % for asthama should be = 11/36 =30.5 % but what you are showing is 40.74 .

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.