Forum Discussion

nonick542's avatar
nonick542
Regular Visitor
10 years ago
Solved

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...
Need Help
  • nonick542's avatar
    nonick542
    10 years ago

    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...

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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))
  • nonick542's avatar
    nonick542
    Regular Visitor
    10 years ago

    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...