Forum Discussion

trulynaveen's avatar
trulynaveen
Frequent Visitor
6 years ago
Solved

Subtotal Issue

Hi Everyone,

 

I'm not able to produce a subtotal in my report. Can anyone please help me to fix this?

 

Formulas I used here are as below: 

 

JobActualHours = IF(HASONEVALUE('Budget Data'[DeptName]),SUMX(FILTER('Jobs Data', 'Jobs Data'[NewJobClass] = SELECTEDVALUE('Budget Data'[DeptName])),('Jobs Data'[JobHours])))
 
KronosHours = IF(HASONEVALUE('Budget Data'[DeptName]),SUMX(FILTER('Kronos Data', 'Kronos Data'[NewJobClass] = SELECTEDVALUE('Budget Data'[DeptName])),('Kronos Data'[Kronoshrs])))

 

Password: bi2020

 

Click the link to get access to the Data File Attachment 

 

 

DeptName is coming from Budget worksheet from the attachment, I have created a Date field (CalendarAuto) and created date relationship with the three worksheets. I want to see the variance for each Department

 

Appreicate your help!

 

 

Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , trulynaveen 

    You need to do same  change in  your  measures .

    Take the measure JobActualHours as a  sample:

     

    JobActualHours = SUMX(FILTER('Jobs Data', 'Jobs Data'[NewJobClass] in FILTERS('Budget Data'[DeptName])),('Jobs Data'[JobHours]))

     

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies