Summarize table

[ThomasWeppler]

Hi Power Bi community

I have made a summarize table that looks at all the invoices diffrent employees send out each month.

Salery = SUMMARIZE(Invoice, Calender[Year_months], invoice[userid], "Faktureret", sum(Invoice[Faktureret]))

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.

[ThomasWeppler · Accepted solution]

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.

[PaulDBrown]

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#M861358 

[ThomasWeppler]

Thanks a ton. This definitely got me further.
I ended up makeing a measure with the all filter like this

Test =

var test_ = CALCULATE(SUM(Salery[Salery]),ALL(calender[Months_year]))

return

if(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.809

 

[PaulDBrown]

Test =
VAR test_ =
    CALCULATE ( SUM ( Salery[Salery] ), ALL ( calender[Months_year] ) )
VAR _calc =
    IF ( ISBLANK ( test_ ), 20000, test_ )
RETURN
    SUMX ( UserTable, _calc )

[ThomasWeppler]

Thanks for the quick response.
You are a champ.
I copied the code in and didn't get the result I wanted but I will study your code a bit more next week and accept the solution if I get it to work.

[PaulDBrown]

You might need to wrap the IF statement with CALCULATE

Test =
VAR test_ =
    CALCULATE ( SUM ( Salery[Salery] ), ALL ( calender[Months_year] ) )
  
RETURN
    SUMX ( UserTable,  CALCULATE(IF ( ISBLANK ( test_ ), 20000, test_ ) ))

[PaulDBrown]

@ThomasWeppler 

Or you can try:

test =
VAR test_ =
    CALCULATE ( SUM ( Salery[Salery] ), ALL ( calender[Months_year] ) )
RETURN
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Salery, UserTable[ID], Calender[Months_year] ),
            "_calc", IF ( ISBLANK ( test_ ), 20000, test_ )
        ),
        [_calc]
    )

[johnt75]

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/ 

[ThomasWeppler]

I have tried your code and it doesn't change anything. I still get the exact same table as I got before.

[johnt75]

do you have some sample data you could share ?

[ThomasWeppler]

Here is a screenshoot of the data I cannot upload a .pbix file

[johnt75]

I 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) )