Measures totals needed to SUM based on selected Date

[Anonymous]

I have 4 measures as per table below and they are working correctly per row, but I'm having issues to get the Total row as a SUM of each column.

How can I get the totals correctly as per table below? (the last row is the result that I want to achieve)

Work Item ID	Date	Scripts	Transactions	70% of Scripts	70% of Transactions	Saved Hours	Saved Amount
11649	1/06/2020	1	1	0.7	0.7	0.76	33.17
11650	1/06/2020	1	5	0.7	3.5	0.99	43.33
11657	1/06/2020	1	3	0.7	2.1	0.87	38.25
20138	1/06/2020	1	7	0.7	4.9	1.11	48.42
21238	1/06/2020	1	3	0.7	2.1	0.87	38.25
22013	2/06/2020	1	1	0.7	0.7	0.76	33.17
22014	2/06/2020	1	5	0.7	3.5	0.99	43.33
22015	2/06/2020	1	5	0.7	3.5	0.99	43.33
22016	2/06/2020	1	3	0.7	2.1	0.87	38.25
22017	2/06/2020	1	3	0.7	2.1	0.87	38.25
	Total	10	36	7	25.2	9.0916	397.7575

Thanks

[Fowmy]

@Anonymous 

Add a table visual 

Measures

70% of Scripts = SUM('Table'[Scripts]) * .7

70% of Transactions = SUM('Table'[Transactions]) * .7

Saved Amount = [Saved Hours] *43.75

Saved Hours = ( [70% of Transactions]* 0.083)+[70% of Scripts]

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , not clear the issue you are facing in this

Try like

70% of Scripts = Sum([Scripts])*0.7
70% of Transactions = Sum([Transactions])*0.7
Saved Hours = ([70% of Transactions ] * 0.083)+[70% of Scripts]
Saved Amount = Sum([Saved Hours]) *43.75

[Greg_Deckler]

@Anonymous - This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

As for your measures themselves, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler 
Yes, this is exactly the issue that I'm having, with the totals.
I'm fine with the measures, I was able to get them right per row, but the totals are not correct for the day/month/year when I use my Date slicer.
I read your post explaining in details, but I only have basic Power BI skills, it couldn't get it to work properly 🙁.
I would really appreciate it if you could kindly help me to create the code for what you explained in my report's case.

This is what I have for the measures:

70% of Scripts = COUNT('Test Scripts'[Work Item Id])*0.7

70% of Transactions = sumx(VALUES('Test Scripts'[Date]), distinctcount('Transactions'[Work Item Id]))*0.7

Saved Hours Total = sumx(VALUES('Test Scripts'[Date]), distinctcount('Transactions'[Work Item Id])*0.083 + DISTINCTCOUNT('Test Scripts'[Work Item Id]))*0.7

Saved Amount Total = [Saved Hours Total] * (350 / 8 )

How should I correctly use the HASONEFILTER with them in order to get the totals displayed as per the total row in my example table above, in order to actually SUM the columns for the respective day or month or year, please?

Thank you very much in advance

[Greg_Deckler · Accepted solution]

@Anonymous - I would use something like HASONVALUE on your Date column. If it has one value, return your row measure. Otherwise, do your SUMMARIZE and SUMX. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907