Forum Discussion
Summarize table
I found a solution to the problem.
In the end I just made a new table in PowerBI desktop manually with the users id number and a month index from 0 to -12 with the month index I made a lookupfuntion to find the Calender[year_months] from my calender tabel.
Now I had a month for each user from there I just used lookup funktions to get all the data I needed in one table
If there was no data to be found I just used the if(isblank,0,tabel[value]) at the end of my calculations.
From there I just added a new colum which calculate the salary in my new tabel.
Thanks a ton for the help Johnt and Paul, I didn't end up using any of your solutions but it definitely helped me in the right direction.
If a moderator read this message just close the thread.
You can use COALESCE to make sure that you return 0 instead of blank, so you could change your code to be
Salery = ADDCOLUMNS( SUMMARIZE(Invoice, 'Calendar'[Year_months], Invoice[user_id]), "Faktureret",
COALESCE( CALCULATE(SUM(Invoice[Faktureret])), 0) )
Its best practice not to use SUMMARIZE to add calculated columns to a summary table but to use ADDCOLUMNS instead - https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/
I have tried your code and it doesn't change anything. I still get the exact same table as I got before.
do you have some sample data you could share ?
Here is a screenshoot of the data I cannot upload a .pbix fileI think I understand the problem. You are summarizing the invoices table and the missing months do not exist in that table, so there is nothing to summarize.
I'm not sure if this will work but you can try summarizing the calendar table instead
Salery = ADDCOLUMNS( SUMMARIZE('Calendar', 'Calendar'[Year_months], Invoice[user_id]), "Faktureret",
COALESCE( CALCULATE(SUM(Invoice[Faktureret])), 0) )