Calculated columns based on filters

[laurocarmo]

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!

[laurocarmo]

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]))

[v-yaningy-msft]

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]

Thanks @ray_aramburo and @v-yaningy-msft 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...

[v-yaningy-msft]

Hi, @laurocarmo 

You can use Google Drive to share your pbix files as a link (you need to set it up to be accessible without an account login).

Best Regards,
Yang
Community Support Team

[ray_aramburo]

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