Forum Discussion

trulynaveen's avatar
trulynaveen
Frequent Visitor
6 years ago
Solved

Cardinality/Relationship Issue

https://tacoteam-my.sharepoint.com/:x:/g/personal/njonnalagadda_tacometals_com/ES--F9WiuqRGkOrsmHxa8coBSTg-7CvMNwN77j9pyg9hTg?e=Jy7qXA 

 

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:

 

NewJobClassTotal MinutesBudget Minutes
SEA5946856764.8

 

Password to the file: BI2020

 

Need Help
Show and Tell
Tips and Tricks
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago
    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:)

5 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    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's avatar
      trulynaveen
      Frequent Visitor
      6 years ago

      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's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago
        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:)
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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