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 file