cancel
Showing results for
Did you mean:  Post Partisan

## Need help in Dax Formula (SUM & group BY)

I have the sample data like below

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

__LastDateTable,
"Value at Last Date",
CALCULATE(
FILTER(
Table2,
[LastDate] = Table2[Date]
&& Table2[DW_ID] = EARLIER( Table2[DW_ID])
)
)
)
RETURN
CALCULATE(
SUMX( __AddColumn, [Value at Last Date] )
)``` 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])
)

__LastDateTable,
"Value at Last Date",
CALCULATE(
FILTER(
Table2,
[LastDate] = Table2[Date]
&& Table2[DW_ID] = EARLIER( Table2[DW_ID])
)
)
)
RETURN
CALCULATE(
SUMX( __AddColumn, [Value at Last Date] )
)```   Post Partisan

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

i have the simple scnerio as below .

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 : Anonymous
Not applicable

can you upload some sample data?  Post Partisan

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