Forum Discussion
Summarize table
Hi Power Bi community
I have made a summarize table that looks at all the invoices diffrent employees send out each month.
But if no invoices are send out I want to get a 0 in the table and not have the colum ommited
If you look closely at the table above you can see that no invoices were send out from the selected userids during October 2021
So the summarize table doesn't add a column for these months.
Ideally I would like to still have a the month added even if but if it is possible I would like to still get the a column for each of the users for October 2021 and than just write 0 rather than leaveing them out completely.
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.
12 Replies
You need to create a dimension table for UserID and use that in the visual with a measure + 0
See this thread for reference:
https://community.powerbi.com/t5/Desktop/ISBLANK-or-Measure-0-not-working/m-p/2401309#M861358Thanks a ton. This definitely got me further.
I ended up makeing a measure with the all filter like thisTest =var test_ = CALCULATE(SUM(Salery[Salery]),ALL(calender[Months_year]))returnif(ISBLANK(test_),20000, test_)
As you can see in the table below I got the right value now, but I still have problems with the sum. Since the employee added 0 to the sum instead of the 20.000. So the sum of jul-21 should be 40.000 and the sum of okt-21 should be 51.809 not 31.809Test = VAR test_ = CALCULATE ( SUM ( Salery[Salery] ), ALL ( calender[Months_year] ) ) VAR _calc = IF ( ISBLANK ( test_ ), 20000, test_ ) RETURN SUMX ( UserTable, _calc )
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 ?
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.
