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