topic Get Sum of a measure in row context in DAX Commands and Tips

Get Sum of a measure in row context

Anonymous — Tue, 24 May 2022 11:29:39 GMT

Hello,

I have a table of Assignments and a table Employees.

I want to get Howmany days in the filter are within every assignment start and end date ... to get the employee booking hours within the selected month and year only

For Example I selected Employee= Keerthi and month=March 2022 ... you can see that the column **Demand_Days_of_task_in_period will only count the days of this assignment that is within my date selection , so only march days are counted while feb and may are not.

The measure formula

** Demand_Days_of_task_in_period = CALCULATE(COUNTROWS('Date'),FILTER('Date','Date'[Date]>=MIN(Assignments[AssignmentStartDate])&&'Date'[Date]<=MIN(Assignments[AssignmentFinishDate])))

The requirement:

I want to access this measure out of row context, meaning that when I add it to a card it will sum all the rows values (6+1+1+1+2+2+2+2+1)= 18 .. but as you can see the card on the top right which is with the same measure is giving me blank, it will only give me a value when I select a specific row ...

I searched for it but found most ways are through creating a calculated column, while the measure here will depend on the user selection of the date

Thanks for help

Re: Get Sum of a measure in row context

Anonymous — Fri, 27 May 2022 03:10:20 GMT

Hi Anonymous ,

You can create another new measure as below base on the current measure [Demand_Days_of_task_in_period] and apply the new measure onto the Card visual...

Measure = SUMX ( GROUPBY ( 'Assignments', 'Assignments'[Employee Name], 'Assignments'[TaskName] ), [Demand_Days_of_task_in_period] )

In addition, you can refer the method in the following links to handle with incorrect total values on the matrix visual...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Get Sum of a measure in row context

Anonymous — Sat, 28 May 2022 07:54:31 GMT

Thank you so much, That helped alot