Forum Discussion

MattSwan's avatar
MattSwan
Frequent Visitor
5 years ago

Missing Totals from Table Visual

Hi there,

 

My first post! Fairly new to Power BI. I think this is an issue with how I've setup my query, but here goes.

 

I have two tables linked via an ID. One containing Student details and one attendance data. It's a one-many relationship.

 

I have created two calculated columns in the attdnance table that result in columns of ones and zeros, which is working fine.

 

I have created a measure that divides the sum of one columns ones by the other columns ones to give a % and this works too.

 

The final step in my measure is a condition that returns a one or zero if a value is below 90.

 

PA Pupils = if(DIVIDE(sum(attendance[Present]),SUM(attendance[Possible]))*100<90,1,0)
 
Now this works and when I copy it into a table that contacins a list of students it gives me ones and zeros where appropriate for each student. What it doesn't give me is a total at the bottom. The total remains zero no matter the number of ones in the column above. I have also tried to display the total in a Card visual and this also gives me zero.
 
Anyone have any suggestions what I might be doing wrong?
 
 
Thanks in advance.

1 Reply

  • moizsherwani's avatar
    moizsherwani
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

    Welcome to the world of PowerBI 😀. One of the first problem Excel migrants face is the same one you are facing (the totals don't add up or aren't correct). The totals in PowerBI don't work as you might think. In your case, when the formula comes to the total part of the table it considers the 9 and the 6 (the totals of the first two columns as per your snippet) and evaulates the formula based on these two values which is why you are probably always getting "0". There is a number of way to add these numbers and without knowing your exact data model / end goals I cannot suggest the best one but one method would be to use an aggregator function such as SUMX (see below) and drag it into the table and see if the correct result shows up in the total (again this may not be correct becase I do not know the table structure for the attendance table)

     

     

     

     

    PA Puplis Total = SUMX (attendance, [PU Pupils])