Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Impactful Individual
4 years ago
Solved

Summarize table

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], "Fa...
Need Help
  • ThomasWeppler's avatar
    ThomasWeppler
    4 years ago

    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.

johnt75's avatar
johnt75
Super User
4 years ago

do you have some sample data you could share ?

ThomasWeppler's avatar
ThomasWeppler
Impactful Individual
4 years ago


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

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

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