Forum Discussion

RhinoceRoss's avatar
RhinoceRoss
Frequent Visitor
3 years ago

PBI Table Total miscalculation

Hello Pbi Community.
I am Currently startting a process to build an ARR model however i am having a confusing issue with the below table.
in the below table is a list of supplier who were expected to renew in a certain month, there previous invoice amount and there renewal invoice and now im checking if there were increases or decreases in value. to eventually build a waterfall chart later. 
when i do a calculation on the variance, the total works fine, it calculated every line to give me a Total, 
however when i do a calculaton to only show me supplier that increased in value, it gives me the SAME Total value, 
my increased value forumula is a if(variance > 0, variance, blank())

It is returning me the right values on a line level but in a TOTAL level it seems to be calculating numbers that arent in that column. why would this be? and how can i be rectified?

any help or guidance will be greatly appreciated.

regards,

Ross

 

 

Need Help

1 Reply

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    It looks like the issue you're experiencing with the total calculation for the "Increased Value" column is caused by the way Power BI is handling the blank values in the column.

    When you use the formula "if(variance > 0, variance, blank())" to filter out only the providers that had an increase in value, Power BI is still including the blank values in the total calculation. This is causing the total to be higher than it should be.

    To fix this issue, you can use the "SUMX" function in combination with the "FILTER" function to only sum the non-blank values in the "Increased Value" column. The formula would look like this:

    =SUMX(FILTER(table, table[Increased Value] <> BLANK()), table[Increased Value])

    This will give you the correct total for the "Increased Value" column, only considering the non-blank values.

    Another alternative is to use the "COUNTROWS" function to count only the rows that have a value in the "Increased Value" column and then multiply it by the average of the "Increased Value" column.

    =COUNTROWS(FILTER(table, table[Increased Value] <> BLANK())) * AVERAGE(FILTER(table, table[Increased Value] <> BLANK()))