Need help in Dax Formula (SUM & group BY)

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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

can you upload some sample data?

[Anonymous]

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