Forum Discussion

laurocarmo's avatar
laurocarmo
Frequent Visitor
2 years ago

Calculated columns based on filters

Dear all,

I hope this post find you all health and safe!

 

I am new on Power BI, but I use Excel for many years.
I am "converting" my Excel analysis to Power BI Desktop, but I found a dificulty now.

There are 3 columns (links quantity, faults quantity and total fault duration) and I need to calculate the average of faults / links and duration / links.

I was able to execute this on Excel, but o DAX not, because these two calculated columns do not change the values when I use fields filters.

 

This is the "normal" situation, without filters

 

And this is the situation using filter (on year selection)

 

See the columns faults quantity and total duration time were updated, but the average columns not.

 

Sorry if this doubt was already solved, but I didn't find it.

 

Thanks in advance and best regards!

Need Help

5 Replies

  • ray_aramburo's avatar
    ray_aramburo
    Super User
    2 years ago

    How are you calculating in Power BI Average Faults/Links and Duration/Links?

  • laurocarmo's avatar
    laurocarmo
    Frequent Visitor
    2 years ago

    Hi ray_aramburo,

    In fact, I was not able to create a formula / measure for this, because it retrieved only one answer.
    One test I did is:

    Medida = CALCULATE(SUM('Cálculo Ranking'[Qtde Serviços]);FILTER('Cálculo Ranking';'Cálculo Ranking'[Provedor]="ACESSOLINE TELECOMUNICACOES LTDA"))

    but the result is not dinamic, but related only to one provider.
     
    Other attempt was:
     
    Coluna = 'Cálculo Ranking'[Qtde Falhas]/'Cálculo Ranking'[Qtde Serviços] IN SELECTEDVALUE(Table1[Ano];2020)

    and this:
     
    Coluna = AVERAGEX('Cálculo Ranking';('Cálculo Ranking'[Qtde Falhas]/'Cálculo Ranking'[Qtde Serviços]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, laurocarmo 

      Thanks for ray_aramburo reply. Would love to help, but you need to share out the logic that achieves the results you want and the pbix file without sensitive data for testing purposes.

      Best Regards,
      Yang
      Community Support Team

      • laurocarmo's avatar
        laurocarmo
        Frequent Visitor
        2 years ago

        Thanks ray_aramburo and Anonymous for the support,

        Sorry for my absence of knowledge, but could you tell me how to share these files?
        I've tried to attach them here, but I was not possible...