Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to fix months

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(SUMMAR...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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