Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate measure (standard deviation) based on selection

Hi power bi community,   currently I struggle with calculating a measure, based on the filter selection. My data looks like the following:   The complete power bi app can be found here.   ...
Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi Anonymous ,

    >>I mean, it is a general problem for me to calculate something based on what there is shown in the power bi matrix.

    In this scenario, we will need to use the measure rather than a calculated column, a calculated column will be calculated on whole data, the filter will not change it.

    We can create a measure using the following DAX query:

    STDEV1 = CALCULATE(STDEV.P(Tabelle1[Revenue]),FILTER(ALL(Tabelle1),Tabelle1[Country] = MIN(Tabelle1[Country]) && Tabelle1[Distinct Monat] in ALLSELECTED(Tabelle1[Distinct Monat])))

    The result will like below: 

    Best Regards,

    Teige

Gopal30's avatar
Gopal30
Helper I
7 years ago

Meanwhile you can try the following formula as "Calculated Column" (Not Measure):

 

STDEV1 = CALCULATE(STDEV.S('Tabelle1'[Revenue]), FILTER('Tabelle1',('Tabelle1'[Country])=EARLIER('Tabelle1'[Country])),FILTER('Tabelle1',(Tabelle1[Distinct Monat]) = EARLIER('Tabelle1'[Distinct Monat])))
 
Check if it works
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Gopal30,

     

    meanwhile I tryed your proposal. Unfortunately I had no success.

     

    Here is what I did (it is a column - no measure):

     

    Any other suggestion what I can do? I mean, it is a general problem for me to calculate something based on what there is shown in the power bi matrix. Average is another example where I want to calculate column total divided by number of rows within the matrix and show this in every row.

     

    Anyway, thank you very much for your reply.

     

    Best regards,

     

    Tom

    • TeigeGao's avatar
      TeigeGao
      Solution Sage
      7 years ago

      Hi Anonymous ,

      >>I mean, it is a general problem for me to calculate something based on what there is shown in the power bi matrix.

      In this scenario, we will need to use the measure rather than a calculated column, a calculated column will be calculated on whole data, the filter will not change it.

      We can create a measure using the following DAX query:

      STDEV1 = CALCULATE(STDEV.P(Tabelle1[Revenue]),FILTER(ALL(Tabelle1),Tabelle1[Country] = MIN(Tabelle1[Country]) && Tabelle1[Distinct Monat] in ALLSELECTED(Tabelle1[Distinct Monat])))

      The result will like below: 

      Best Regards,

      Teige

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi TeigeGao,

         

        checking this post, I think have similar issue when it comes to add a standard deviation as a measure.

         

        I have a pivot table with data coming from my data model, showing following data:

         

        - Week numbers in columns [weeknum]

        - Product ID in rows [Producto]

        - Quantity consumed as Σ [Cantidad]

         

        Point is, a lot of products have zero consumption along the year.

        Here comes a screenshoot (week numbers reduced for pasting smaller picture):

         

         

        What I want is to add a measure showing the total standard deviation as a column at the end based on column selection (eventually will be 52 weeks).

         

        Can you advise?

         

        Thanks,