Reply
Regular Visitor
Posts: 17
Registered: ‎12-17-2015
Accepted Solution

What is the %GT Quick Calculation really?

Using the %GT quick calculation and realising that I couldn't change the name of it, I tried to use the formula that is generally described as the replacement.

 

Percentage = SUM(TableName[MyColumn]) / CALCULATE(SUM(TableName[MyColumn]), ALL(TableName))

However, when I went to use a filter on my report page, I noticed that the formula did not deliver the expected result.  

After quickly testing side by side the results of GT% quick calc and the above formula calcuation I worked out only the GT% quick calc was giving me the changed view of my data that I wanted.

 

Is there anyone who knows what the formula for GT% quick calc is exactly so I can mimic its behaviour in more situations?


Accepted Solutions
Moderator
Posts: 4,364
Registered: ‎03-06-2016

Re: What is the %GT Quick Calculation really?

Hi @KeithSuckling,

 

As the measure use the ALL() function which returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. It's expected that when you check different values from the slicer, the chart values will not change.

 

Regarding the "Percent of grand total", any filters include slicer, visual/ page/ report level filters will impact values.

 

In your scenario, you can use ALLSELECTED() function in the measure,

 

Measure 2 = SUM(Table1[Person count])/CALCULATE(SUM(Table1[Person count]),ALLSELECTED('Table1'))

 

w1.PNGw2.PNG

 

 

Best Regards,
Qiuyun Yu

 

 

View solution in original post

Attachment

All Replies
Super Contributor
Posts: 1,469
Registered: ‎06-24-2015

Re: What is the %GT Quick Calculation really?

Hi @KeithSuckling

 

What I complete the steps that you did I get exactly the same results between the Quick measure and the created measure.

 

I am unsure how yours is different?

 

Delete later - IP Address Table.png

Regular Visitor
Posts: 17
Registered: ‎12-17-2015

Re: What is the %GT Quick Calculation really?

@guavaq below is the pic that shows a replication of the problem.  The data file is incredibly simple, the two fields are very simple as well.  When the filter is used the output of the formula and the GT% quick calc is different.

 

Capture.JPG

 

 

Super Contributor
Posts: 1,469
Registered: ‎06-24-2015

Re: What is the %GT Quick Calculation really?

Hi @KeithSuckling

 

Could I please ask you to try the following formula, and see if that resolves your issue.

 

DIVIDE(
	sum(Table1[Person count]),
  Calculate(
	sum(Table1[Person count]),
     ALL(Table1)
  )
)
Highlighted
Regular Visitor
Posts: 17
Registered: ‎12-17-2015

Re: What is the %GT Quick Calculation really?

Thank for trying @guavaq

I had tried this already and it hadn't worked.  

The picture below confirms the new formula produces the same result.

 

Capture.JPG

Moderator
Posts: 4,364
Registered: ‎03-06-2016

Re: What is the %GT Quick Calculation really?

Hi @KeithSuckling,

 

As the measure use the ALL() function which returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. It's expected that when you check different values from the slicer, the chart values will not change.

 

Regarding the "Percent of grand total", any filters include slicer, visual/ page/ report level filters will impact values.

 

In your scenario, you can use ALLSELECTED() function in the measure,

 

Measure 2 = SUM(Table1[Person count])/CALCULATE(SUM(Table1[Person count]),ALLSELECTED('Table1'))

 

w1.PNGw2.PNG

 

 

Best Regards,
Qiuyun Yu

 

 

Attachment
Regular Visitor
Posts: 17
Registered: ‎12-17-2015

Re: What is the %GT Quick Calculation really?

Thanx @v-qiuyu-MSFT!!!

That seems to work, producing the same result as the GT% quick calc.

 

Capture.JPG