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.
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
Thanks a ton. This definitely got me further.
I ended up makeing a measure with the all filter like this
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
Test = VAR test_ = CALCULATE ( SUM ( Salery[Salery] ), ALL ( calender[Months_year] ) ) VAR _calc = IF ( ISBLANK ( test_ ), 20000, test_ ) RETURN SUMX ( UserTable, _calc )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_ ) ))