topic Re: Incorrect Totals for Percentage Values in Table in DAX Commands and Tips

Incorrect Totals for Percentage Values in Table

Will_Rayner — Wed, 02 Mar 2022 09:50:42 GMT

I have three tables, Projections, Actuals, and % Variation. The Variation is calculated using the measure

Volume (Variation) = CALCULATE([Volume (Actuals)])/CALCULATE([Volume (Projection)])-1

Volume (Actuals) and Volume (Projection) are calculated as follows from the main data table. The volume column is just a simple value of 1 for each record to enable counts.

Volume (Actuals) = CALCULATE(SUM('MASTER TABLE'[Volume]),'MASTER TABLE'[Type]="Actuals")

Volume (Projection) = CALCULATE(SUM('MASTER TABLE'[Volume]),'MASTER TABLE'[Type]="Projection")

The individual % calculations in the table are correct, however using the same logic, and using February as an example, the Total line for % variation should be 12% not 8%

Why is the total not calculating correctly, and how can I correct it?

Re: Incorrect Totals for Percentage Values in Table

amitchandak — Wed, 02 Mar 2022 10:40:32 GMT

Will_Rayner , seems fine to me. Try these with a small difference

Volume (Actuals) = CALCULATE(SUM('MASTER TABLE'[Volume]),filter('MASTER TABLE','MASTER TABLE'[Type]="Actuals")
Volume (Projection) = CALCULATE(SUM('MASTER TABLE'[Volume]),filter('MASTER TABLE''MASTER TABLE'[Type]="Projection")

Volume (Variation) = divide([Volume (Actuals)],[Volume (Projection)])-1

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Incorrect Totals for Percentage Values in Table

Will_Rayner — Wed, 02 Mar 2022 10:52:18 GMT

Thank you for the response.

I have used the suggested alternative calculation for the Volume Variation, however this still returns the incorrect value in the total line. i.e. it is still showing 8% for February rather than the expected result of 12%.

Re: Incorrect Totals for Percentage Values in Table

amitchandak — Wed, 02 Mar 2022 11:54:09 GMT

Will_Rayner ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Incorrect Totals for Percentage Values in Table

Will_Rayner — Wed, 02 Mar 2022 12:23:23 GMT

The data source is very simple and could easily be replicated. I'm not quite sure how to do that effectively here as the tables are large and do contain sensitive data, as does the pbxi file of which this is a tiny element.

I think it is the logic of PBI that is the issue, i.e. treating the variation measure differently in the table results, which are correct (the (1%), 34%, (2%), 197% & 5% for February), but it calculates the TOTAL differently i.e 1,371.88 against a projection of 1,225.45 should be 12% and not 8% as shown.

Re: Incorrect Totals for Percentage Values in Table

Whitewater100 — Wed, 02 Mar 2022 13:50:07 GMT

Hi:

You can use SUMX to fix:

=SUMX(

Total Measure =

var vtable = SUMMARIZE('MASTER TABLE',

'MASTER TABLE'[Description],

"Variance", Volume (Variation))

return

IF(HASONEVALUE( 'MASTER TABLE'[Description]),[Volume Variation], SUMX(vtable,[Variance]))

Re: Incorrect Totals for Percentage Values in Table

Whitewater100 — Wed, 02 Mar 2022 13:52:06 GMT

Volume Variation(New) =

var vtable = SUMMARIZE('MASTER TABLE',

'MASTER TABLE'[Description],

"Variance", Volume (Variation))

return

IF(HASONEVALUE( 'MASTER TABLE'[Description]),[Volume Variation], SUMX(vtable,[Variance]))

Re: Incorrect Totals for Percentage Values in Table

Will_Rayner — Wed, 02 Mar 2022 14:48:12 GMT

Hi,

Is this solution intended to calculate the variance between the Projection count and the Actual Count? I can't see how those inputs come into this formula.

I was hoping for a DAX solution that, from the measures I already have in place, I could copy and paste as a new measure to use?

Re: Incorrect Totals for Percentage Values in Table

Whitewater100 — Wed, 02 Mar 2022 15:26:52 GMT

Hi:

I guess seeing a sample file will help. Usually the SUMX fix works on totals, so I'm not sure why this did not work in this case.

Can you change your volume % chg measure to:

Volume (Variation) = DIVIDE([Volume (Actuals)], [Volume (Projection)])-1

and see if that helps, then use the new code?

Re: Incorrect Totals for Percentage Values in Table

Will_Rayner — Wed, 02 Mar 2022 16:08:54 GMT

A simplified MASTER TABLE would be:

Type	Description	Start Date	Volume
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Slips	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Actual	Declarations (All)	01/02/2022	1
Projection	Slips	01/02/2022	6
Projection	Declarations (All)	01/02/2022	5

On which my measures (and your new DIVIDE one will work.

The expected results from this should be +20% for Declarations (All), +50% for Slips, and +36% as a Total

The visualisations are simple tables

Should your SUMX be a measure? This could be where I am going wrong?

Re: Incorrect Totals for Percentage Values in Table

Whitewater100 — Wed, 02 Mar 2022 18:27:55 GMT

Hi:

I created your data and can paste link to file here. I hope this helps. You'll notice one solution is with one fact table and the same solution with two fact tables. (which you described)

https://drive.google.com/file/d/1jGo9p026vlCCsCcXkmmbqp0YaSo0cUhv/view?usp=sharing

Re: Incorrect Totals for Percentage Values in Table

Will_Rayner — Mon, 07 Mar 2022 09:06:54 GMT

Hi,

Many thanks for your solution here, and from your image, it looks like this is doing as I require, however unfortunately despite a few attempts, I am unable to open any of your solution documents in a way that they make any sense to me. I just see filles of strange symbols!

Are you able to post a solution as as response rather than links to files? This may then assist other users with a similar problem

Many thanks

Re: Incorrect Totals for Percentage Values in Table

Whitewater100 — Mon, 07 Mar 2022 14:13:25 GMT

Hi Will:

I'm not sure why the shared google file won't open. I'll try something else. I wanted you to see the data model. The results for your inital question are:

Energy.pbix - Google Drive

I download from here.

I can see it fine. Is this any better?

Re: Incorrect Totals for Percentage Values in Table

Anonymous — Tue, 08 Mar 2022 07:47:40 GMT

Hi Will_Rayner ,

Note that in a Power BI visual, the total of a measure is not just the sum of all rows. it is a calculated value affected by measure.

If you want to calculate the sum of measure value, please use sumx() function.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

total = sumx(filter(allselected('table'),expression),[measure])

And if you want to show [measure] on row level and show total on total level, please use ISINSCOPE() or ISFILTERED() function.

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

Please refer the formula.

total =
var _total = sumx(filter(allselected('table'),expression),[measure])
return

IF(ISINSCOPE([row field]),[measure],_total)

Best Regards,

Jay