How to fix months

[Anonymous]

Hi to all.

I have some measure the give me correct answer for the month that is selected,

but I want to fix certain months (2022-8, 2022-9, 2022-10).

The measure is

AVERAGEX(ADDCOLUMNS(SUMMARIZE(Sales,'Sales'[POS code]), "No", CALCULATE(DISTINCTCOUNT(Sales[Sales Program]))), [No])

I hope you understand my question.

Thanks in advance.

[Anonymous]

Hi @Anonymous ,

Is the filter for these months an empty set? If you want to show 0, try this.

AVERAGEX (
    ADDCOLUMNS (
        SUMMARIZE ( Sales, 'Sales'[POS code] ),
        "No", CALCULATE ( DISTINCTCOUNT ( Sales[Sales Program] ) + 0 )
    ),
    [No]
)

If I have misunderstood your question, please feel free to contact me. It is best to post sample data and expected output.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Hi Gao, thanks for reply.

I can not share my date, sorry. Maybe this informations will help.

I tried measure like this, but I did not get correct result:

VAR M_8 = "2022-8"
VAR M_9 = "2022-9"
VAR M_10 = "2022-10"
VAR AVG_8 = AVERAGEX(ADDCOLUMNS(SUMMARIZE(FILTER(Sales, Sales[Y/M]=M_8),'Sales'[POS code]), "No", CALCULATE(DISTINCTCOUNT(Sales[Sales Program]) )), [No])
VAR AVG_9 = AVERAGEX(ADDCOLUMNS(SUMMARIZE(FILTER(Sales, Sales[Y/M]=M_9),'Sales'[POS code]), "No", CALCULATE(DISTINCTCOUNT(Sales[Sales Program]) )), [No])
VAR AVG_10 = AVERAGEX(ADDCOLUMNS(SUMMARIZE(FILTER(Sales, Sales[Y/M]=M_10),'Sales'[POS code]), "No", CALCULATE(DISTINCTCOUNT(Sales[Sales Program]) )), [No])
VAR AVG_3m = (AVG_10+AVG_8+AVG_9)/3
RETURN
AVG_3m

 The report looks like this:

[Anonymous]

Hi @Anonymous ，

Please try this measure.

Avg SKU 3m =
VAR M_8 = "2022-8"
VAR M_9 = "2022-9"
VAR M_10 = "2022-10"
VAR AVG_8 =
    CALCULATE (
        AVERAGEX (
            ADDCOLUMNS (
                SUMMARIZE (
                    FILTER ( ALLSELECTED ( Sales ), Sales[Y/M] = M_8 ),
                    'Sales'[POS code]
                ),
                "No", CALCULATE ( DISTINCTCOUNT ( Sales[Sales Program] ) )
            ),
            [No]
        ),
        ALL ( 'Sales'[Year], 'Sales'[Month] )
    )
VAR AVG_9 =
    CALCULATE (
        AVERAGEX (
            ADDCOLUMNS (
                SUMMARIZE (
                    FILTER ( ALLSELECTED ( Sales ), Sales[Y/M] = M_9 ),
                    'Sales'[POS code]
                ),
                "No", CALCULATE ( DISTINCTCOUNT ( Sales[Sales Program] ) )
            ),
            [No]
        ),
        ALL ( 'Sales'[Year], 'Sales'[Month] )
    )
VAR AVG_10 =
    CALCULATE (
        AVERAGEX (
            ADDCOLUMNS (
                SUMMARIZE (
                    FILTER ( ALLSELECTED ( Sales ), Sales[Y/M] = M_10 ),
                    'Sales'[POS code]
                ),
                "No", CALCULATE ( DISTINCTCOUNT ( Sales[Sales Program] ) )
            ),
            [No]
        ),
        ALL ( 'Sales'[Year], 'Sales'[Month] )
    )
VAR AVG_3m = ( AVG_10 + AVG_8 + AVG_9 ) / 3
RETURN
    AVG_3m

I don't know the full filtering context, please check if ALL() helps.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum