Subtotal Issue

[trulynaveen]

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!

[Anonymous]

@trulynaveen would you mind sharing how you connected your Kronos data? Was it using the REST API an if so how did you do this?

[amitchandak]

@trulynaveen 

I think your hasonevalues are stopping them . Why those are needed.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[trulynaveen]

Sorry this is not helpful. Appreciate your response!

[v-easonf-msft · Accepted solution]

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.