Sum and average based on selection

[Balhemkan]

• Hello Team, 

I need your help on below issue. Please help me

I am using date slicer (start date and end date) in my dashboard

my problem is, If i select only one month in slicer it should show sum of sales for that month

but if i select more than one month then it should show average of those months

example: i selected Jan5th to Jan 20th in slicer

then it should take from Jan 1st to Jan 31st and should give total of that month.

If i select from Jan 5th to Feb 10th then it should take Min value as Jan 1st and end date will be feb 28th and it should show average of two months.

Please help me with dax. 

Thanks, 

Balhemkan

[Balhemkan]

Hi, 

I mean if only one month is selected in slicer then sum of that month. If two months selected in slicer sum of 1st +sum of 2nd month average we want. 

Could you please suggest on this

[Jihwan_Kim]

Hi,

I think my sample does not suit your inquiry. Could you please share your sample pbix file's link (onedrive, googledrive, dropbox, any others) here, and also the desired result, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[Balhemkan]

Hi, 

Sorry to bother you. Do you have any update on pbix. I'm completely depending on you. 

[Jihwan_Kim]

Hi,

Thank you for your shring the link.

I checked your sample, but I am not sure if it looks like below.

If so, I think the measure shows the correct number.

Or, could you please describe what numbers you want to see as a result?

[Balhemkan]

If one month is selected in slicer then it should be 48355.If two months is selected in slicer then it should be 48355/2 =24177.5 like this

[Anonymous]

Hi @Balhemkan ,

Please refer to my pbix file to see if it helps you.

Best Regards

Community Support Team _ Polly

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

[Balhemkan]

This is not working on card visual and it showing blank. Could you please suggest me

[Anonymous · Accepted solution]

Hi @Balhemkan ,

The measure calculates based on the date column. There is no date on the card so that it is blank. You can use KPI visual to show.

Or use another measure in the card.

Measure2 =
VAR _min =
    MIN ( 'Table'[Date] )
VAR _max =
    MAX ( 'Table'[Date] )
VAR _sales =
    CALCULATE (
        SUM ( volume[Sales] ),
        FILTER ( volume, volume[month] = MONTH ( _min ) )
    )
VAR _diff =
    DATEDIFF ( _min, _max, MONTH ) + 1
RETURN
    _sales / _diff

Best Regards

Community Support Team _ Polly

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

[Balhemkan]

It really works. 

Thank a lot

[Balhemkan]

Hi @Jihwan_Kim 
Actually your measure is right but for my dataset it is not working. I have attached pbix. Could you please help me dax in that file.Pbix Attachment 

[Jihwan_Kim]

Hi, 

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Expected result measure: =
VAR _calendartable =
    SUMMARIZE ( 'Calendar', 'Calendar'[Month] )
VAR _monthlyqty =
    ADDCOLUMNS (
        _calendartable,
        "@qty",
            CALCULATE (
                SUM ( Sales[Quantity] ),
                ALLEXCEPT ( 'Calendar', 'Calendar'[Month Name], 'Calendar'[Month] )
            )
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( _calendartable ) = 1, SUMX ( _monthlyqty, [@qty] ),
        COUNTROWS ( _calendartable ) > 1, AVERAGEX ( _monthlyqty, [@qty] )
    )