Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nonick542
Regular Visitor

CALCULATE not showing correct count

preciso de uma ajuda no power BI em uma soma condicional.

 

tenho 01 tabela (gigante) com datas de voos x nome.

 

Já fiz a soma (qt) de voos por ano, e tenho uma franquia anual.

 

Agora preciso calcular a quantidade de voos que ultrapassaram a franquia anual em um período maior, mas o power bi não está procedendo com esta conta.

 

Fiz uma MEDIDA usando IF, e, qd passo para a tabela no relatório, tenho:

- quantidades anuais (voos e extra-franquia) corretas (ano a ano dá o resultado desejado)

- quantidade no TOTAL do período INCORRETA, pois o BI calcula a franquia como um TODO, e não por período

 

    ** exemplo:

  • se franquia anual = 1.000 voos
  • realizados:
    • ano 1= 500
    • ano 2 =1.500  => extra-franquia = 500
    • ano 3 = 2.000 => extra-franquia = 1.000
    • ano 4 = 100
    • ano 5 = 800
  • O power bi indica na tabela do relatório para extra-franquia:
    • ano 1= 0 (correto)
    • ano 2 = 500 (correto)
    • ano 3 = 1.000 (correto)
    • ano 4 = 0 (correto)
    • ano 5 = 0 (correto)
    • total = 0 (INCORRETO!)  => O BI calcula que a franquia do período é de 5.000 voos, e a soma de voos nos 5 anos foi inferior a este total. O valor correto seria de 1.500 voos extra-franquia, uma vez que deseja-se a SOMA DO QUE ULTRAPASSOU A FRANQUIA A CADA ANO

 

Como posso resolver isso?

 

abs

1 ACCEPTED SOLUTION

hi there


I reached the solution (not the more elegant, but...)

 

4ano = IF(SUM('f_tabelão'[PAX 4])>(CALCULATE(('d_AuxQtMáxPAX'[Qt Máx PAX contrato]);'f_tp Contrato FPSO'[Tp Contrato (anos)]=4));CALCULATE(((SUM('f_tabelão'[PAX 4])-('d_AuxQtMáxPAX'[Qt Máx PAX contrato]))*'d_custos vôos extras'[PAX Extra (só convert R$)]);'f_tp Contrato FPSO'[Tp Contrato (anos)]=4);0)

 

f_tabelão'[PAX 4] -> Unfortunately, I needed to create different columns for each year...

 

 

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

I only speak English, but if I am understanding the crux of your issue, it looks like you are using a Measure in your table. The thing with Measures in tables is that the Total line provides its own context for the Measure and the Measure respects that context. So, if in the context of the Total row, the Measure calculates to 0, that is what it will display. Measures in Total lines in tables do NOT simply add up the sum of the numbers in the column like a table using a Column would do.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

i there

Tks, I'll try to do this in English.

I do have a (giant) table with PAX flown dates and destinies (fig 1), and for each destiny I do have a maximum contract allowance (like a “pre-paid PAX flights”).

 

Capturar.JPG

 

 

 

Capturar5.JPG

 

 

 

 

I need to check and SUM the PAX amount that were over the contract allowance for each year and per destiny. * obs: when we do have total flights bellow this contract allowance, this “credit” is not cumulative for the next period.

 

 

So, I can SUM the total PAX per year and per destiny (no problem), but when I try to calculate for a long period (such as 5 years), the Power BI calculate with the total allowance (ex: 5 x each year allowed) by the total PAX.

 

Capturar3.JPG

 

 

 

 

 

And I need to know the total extra-cost.

 

What can I do?

 

 

The simplest thing to do is to turn your Measure into a Column. So if you had a related table of just "ano 1", "ano 2", etc. relate that to your other table and use RELATEDTABLE function to perform the calculation for each year. Then, you could just use that column in a table visualization along with the year and you are all set as Columns behave the way you would expect in tables.

 

Another option is to use HASONEFILTER(table[ano]) in your original measure calcuation. So, for example:

 

extra-franquia = IF(HASONEFILTER(ano[ano]),IF(SUM(ano[amount])<1000,0,SUM(ano[amount])-1000),-100)

This will return -100 in the Total row because that row does not have only one filter for ano[ano] column (I named my table "ano"). Now, what that calculation would be, not sure because you can't SUM an measure but if you can come up with the right formula for the Total row for the measure, that is how you could do it.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

This just might do it:

 

extra-franquia = IF(HASONEFILTER(ano[ano]),IF(SUM(ano[amount])<1000,0,SUM(ano[amount])-1000),SUMX(FILTER(ano,[amount]>1000),[amount]-1000))

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

hi there


I reached the solution (not the more elegant, but...)

 

4ano = IF(SUM('f_tabelão'[PAX 4])>(CALCULATE(('d_AuxQtMáxPAX'[Qt Máx PAX contrato]);'f_tp Contrato FPSO'[Tp Contrato (anos)]=4));CALCULATE(((SUM('f_tabelão'[PAX 4])-('d_AuxQtMáxPAX'[Qt Máx PAX contrato]))*'d_custos vôos extras'[PAX Extra (só convert R$)]);'f_tp Contrato FPSO'[Tp Contrato (anos)]=4);0)

 

f_tabelão'[PAX 4] -> Unfortunately, I needed to create different columns for each year...

 

 


@smoupre wrote:

I only speak English, but if I am understanding the crux of your issue, it looks like you are using a Measure in your table. The thing with Measures in tables is that the Total line provides its own context for the Measure and the Measure respects that context. So, if in the context of the Total row, the Measure calculates to 0, that is what it will display. Measures in Total lines in tables do NOT simply add up the sum of the numbers in the column like a table using a Column would do.


Portuguese speaker here. Yes, that's exactly what he meant. He would like the total for that measure to display simply the sum of the numbers. Each row represents how much the monthly quota has been exceeded. He wants the sum - in that year - of the excess. Instead, the total sums up the monthly quotas and the actual consumption, indicating that the "total quota" hasn't been exceeded. Any ideas on how he can accomplish this?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.