Incorrect Totals for Percentage Values in Table

[Will_Rayner]

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?

[v-jayw-msft · Accepted solution]

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

[Whitewater100]

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]))

[Whitewater100]

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]))

[Will_Rayner]

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?

[Will_Rayner]

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%.

[Whitewater100]

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

[Will_Rayner]

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

[Whitewater100]

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?

[Will_Rayner]

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?

[Whitewater100]

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? 

[amitchandak]

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

[Will_Rayner]

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.

[amitchandak]

@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.