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.

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

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's avatar
PaulDBrown
Community Champion
4 years ago

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