Cardinality/Relationship Issue

[trulynaveen]

https://tacoteam-my.sharepoint.com/:x:/g/personal/njonnalagadda_tacometals_com/ES--F9WiuqRGkOrsmHxa8... 

Hello everyone,

I'm trying to build a variance report on Power BI and I'm stuck here in building relationships with the two tables. I would much appreciate if you could give me the right direction to get my desired output.

I have two tables Date-Dup which has Department and Date wise budget minutes, I would like to compare this table with Jobs Table which has total minutes by datee

This is the desired output I'm trying to get on BI as of 1/27/2020:

NewJobClass	Total Minutes	Budget Minutes
SEA	59468	56764.8

Password to the file: BI2020

[v-kelly-msft]

Hi @trulynaveen ，

Try the following measures:

Budget = CALCULATE(SUM('Date-Dup'[Budget Minutes]),FILTER(ALLSELECTED('Date-Dup'),'Date-Dup'[Date]<= MIN('Date'[Date])))

Total Minute = CALCULATE(SUM('JobsData'[Total Minutes]),FILTER(ALLSELECTED('JobsData'),'JobsData'[EntryDate]<=MIN('Date'[Date])))

And you will see :

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[JarroVGIT]

Hi @trulynaveen ,

This can be accomplished without an active relationship. I loaded your data into PBI (big thank you for providing data, that really helps us helping you!) and created the following measures:

Budgeted Minutes = SUMX(FILTER(DateDup, DateDup[Date] <= TODAY()), [Budget Minutes])

Actual Minutes = 
SUMX(FILTER(JobsData, JobsData[NewJobClass] = SELECTEDVALUE(DateDup[DeptName])), [Total Minutes])

Then I created a table visual and put in the column DateDup[DeptName] and the two measures, resulting in this:

Does this answer you question?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[trulynaveen]

Thank you so much for the help. I just refreshed my data and wide opened to other departments by removing additional filters,now looks like your formula has worked. But I couldn't see the grand total under actual minutes. 

Also, is there anyway we can put this on a Line Chart using CalendarAuto()?

 Best,

Naveen

[JarroVGIT · Accepted solution]

Hi, change the measure to this:

Actual Minutes =
IF(HASONEVALUE(DateDup[DeptName]),
SUMX(FILTER(JobsData, JobsData[NewJobClass] = SELECTEDVALUE(DateDup[DeptName])), [Total Minutes]), SUM(JobsData[Total Minutes))

This was typed on phone so forgive any typos.
For your graph question, that is a whole new requirement, please open a separate topic for that question with all necessary information so my peers can have a look at that as well:)

[trulynaveen]

This worked for me!