Measure to add all of the scores where date is the same AND Department is the same

[LewisFoulger]

Hello, I am not sure if this is the correct area to be writing this but here goes anyway:

I am making a dashboard to represent all of the data collecting from my weekly audit data app. I have tried previously to create with a data source and it just didnt work so had to rewrite everything.

In this instance of data I have decided to store the data in separate lines depending on the subject of the score as shown:

This is test data I created.

I did it this way to show the data in a radar chart.

I would like to work out the total score of each of the weeks over a monthly trend.

How do i calculate the departments weekly score on the same day?

[Whitewater100 · Accepted solution]

Hello:

Youcan check out this file.

https://drive.google.com/file/d/12Bo0BfUhqd67aafMSQxMUW4_vBZxgbXl/view?usp=sharing 

It's super-important to have a date table (marked as such) and I could have made separate lookup tables for all of your dimensions. You can still slice by them and get weekly scores.

I hope this helps!

[LewisFoulger]

Hi,

I am new to this side of BI so am learning how to make a date table as it seems a useful tool, have you a tutorial you recommend I try and use?

I have created a separate dates tables and got this far: 

I tried to create a new measure but it says it could not find date?

Am I missing a step?

[LewisFoulger]

I got around it, instead of adding measures I had to add a column. 

Thank you for your help!

[amitchandak]

@LewisFoulger , You have week date only, you can join this with a date table and have week and month again there and use those in visual 

You can use sum(Table[Score]) in trend

if you want compare WOW, you can refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.