How to Sum by Group and also filter "slicer"

[NilR]

and I like to calculate based on the GP_NB column.

here is my syntax:

MBRS_MOS = 
VAR _mindate = MIN('Dynamic Calendar Table'[Date])
VAR _maxdate = MAX('Dynamic Calendar Table'[Date])
VAR _gpnb = MAX(GROUP_DIM[GP_NB])
RETURN
CALCULATE(
    SUM('0310 ERUC'[MBR_MOS]),
    FILTER(
        ALLEXCEPT('0310 ERUC','0310 ERUC'[CLAIM_TYPE_UC_SPLIT],'0310 ERUC'[DIST_VISIT]), ('0310 ERUC'[CLM_DATE] >= _mindate && '0310 ERUC'[CLM_DATE] <= _maxdate) && '0310 ERUC'[GP_NB]=_gpnb))

However, I would like to only sum by the GP_NM

[v-deddai1-msft]

Hi @NilR ,

Thank you for sharing the solution. You can also try to accept your last reply as answer to help others find it more quickly.

Best Regards,

Dedmon Dai

[NilR · Accepted solution]

@v-deddai1-msft    I tweaked  @amitchandak  code to Sumx(Distinict and worked. I am so sorry that I was not cleared I mostly needed to sum distinct value of GP_NP. Thanks both for help! also site doesn't let me to rate your answers or select the best answer! sorry!!

MBR_MOS = 
VAR _mindate = MINX(allselected('Dynamic Calendar Table'),'Dynamic Calendar Table'[YearMonth])
VAR _maxdate = MAXX(allselected('Dynamic Calendar Table'),'Dynamic Calendar Table'[YearMonth])
VAR _gpnb = MAXX(allselected(GROUP_DIM),GROUP_DIM[GP_NB])
RETURN
CALCULATE(
SUMX(DISTINCT('0310 ERUC'[MBRS_MOS]),'0310 ERUC'[MBRS_MOS]),
FILTER( '0310 ERUC', ('0310 ERUC'[CLM_YEARMONTH] >= _mindate && '0310 ERUC'[CLM_YEARMONTH] <= _maxdate) && ('0310 ERUC'[CLM_PD] >= _mindate && '0310 ERUC'[CLM_PD] <= _maxdate) && '0310 ERUC'[GP_NB]=_gpnb)
,ALLEXCEPT('0310 ERUC','0310 ERUC'[CLAIM_TYPE_UC_SPLIT],'0310 ERUC'[DIST_VISIT]))

[v-deddai1-msft]

Hi @NilR ,

I don't understand your expected output. Would you please explain more about it. You just want MBRS_MOS is grouped by  GP_NB  and effected by slicer?

Is the following output you want?

Please try :

MBRS_MOS1 = 
VAR _mindate = MIN('Dynamic Calendar Table'[Date])
VAR _maxdate = MAX('Dynamic Calendar Table'[Date])
VAR _gpnb = MAX(GROUP_DIM[GP_NB])
RETURN
CALCULATE(
    SUM('0310 ERUC'[MBR_MOS]),
    FILTER(
        ALLSELECTED('0310 ERUC'), ('0310 ERUC'[CLM_DATE] >= _mindate && '0310 ERUC'[CLM_DATE] <= _maxdate) && '0310 ERUC'[GP_NB]=_gpnb))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @NilR ，

Would you please check if you have create a one to many relationship between Dynamic Calendar Table and 0310 ERUC table by date column. Just delete the relationship , the slicer will work. 

If it still doesn't work, would you please share sample data by onedrive for business.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[NilR]

https://drive.google.com/file/d/1VIil8UNaNlBvQqroKy-BBwZJm2Lp1hbR/view?usp=sharing 

please let me know if you can't open the file! Thanks!

[NilR]

It didn't work. let me see if I can

[amitchandak]

@NilR , Not very clear what output you need and what is wrong

try like

MBRS_MOS =
VAR _mindate = MINX(allselected('Dynamic Calendar Table'),'Dynamic Calendar Table'[Date])
VAR _maxdate = MAXX(allselected('Dynamic Calendar Table'0,'Dynamic Calendar Table'[Date])
VAR _gpnb = MAXX(allselected(GROUP_DIM),GROUP_DIM[GP_NB])
RETURN
CALCULATE(
SUM('0310 ERUC'[MBR_MOS]),
FILTER( '0310 ERUC', ('0310 ERUC'[CLM_DATE] >= _mindate && '0310 ERUC'[CLM_DATE] <= _maxdate) && '0310 ERUC'[GP_NB]=_gpnb)
,ALLEXCEPT('0310 ERUC','0310 ERUC'[CLAIM_TYPE_UC_SPLIT],'0310 ERUC'[DIST_VISIT]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[NilR]

Here is the sample file: https://drive.google.com 

Thank you!

[NilR]

Thanks for helping me!

MBRS_MOS = 
VAR _mindate = MINX(allselected('Dynamic Calendar Table'),'Dynamic Calendar Table'[Date])
VAR _maxdate = MAXX(allselected('Dynamic Calendar Table'),'Dynamic Calendar Table'[Date])
VAR _gpnb = MAXX(ALLSELECTED(GROUP_DIM),GROUP_DIM[GP_NB])
RETURN
    CALCULATE(
    SUM('0310 ERUC'[MBR_MOS]),
    FILTER('0310 ERUC', ('0310 ERUC'[CLM_DATE] >= _mindate && '0310 ERUC'[CLM_DATE] <= _maxdate) && '0310 ERUC'[GP_NB]=_gpnb), ALLEXCEPT('0310 ERUC','0310 ERUC'[CLAIM_TYPE_UC_SPLIT],'0310 ERUC'[DIST_VISIT]))

still not the result i need 😞 😞 

[amitchandak]

@NilR ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.