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(SUMMARIZE(Sales,'Sales'[POS code]), "No", CALCULATE(DISTINCTCOUNT(Sales[Sales Program]))), [No])

 

I hope you understand my question.

Thanks in advance.

Need Help

3 Replies

  • 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