Total % in measure formula rather than a month by month %

[michael_knight]

Hi,

I have a measure that I'm in the process of creating. It's fairly simple but I'm struggling. 

Target & Cancelled. % = [C.]/[C, DS, OA, OS, DP]* [App Tar - RHB Count]

 

The measure that I'm creating will be the Line value.The problem I'm having is that the first part of the measure (

[C.]/[C, DS, OA, OS, DP]) is month by month, however I want that to be a grand total rather than the total for the month. 

If you have any question please don't hesitate to ask

Thanks,

Mike

[Anonymous]

You may use All(table) command to remove the filters and calculate the sum for all the rows.

Function and usage Description

ALL()	Removes all filters everywhere. ALL() can only be used to clear filters but not to return a table.
ALL(Table)	Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario.
ALL (Column[, Column[, …]])	Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario.
ALLEXCEPT(Table, Column1 [,Column2]...)	Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

Abhinav K
-----------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi @Anonymous ,

Thanks for the reply. Where abouts would I put theta ALL() code in my measure?

Would I put it in the measue I posted or original measure of [C] and [C, DS, OA, OS, DP]?

Thanks,

Mike

[Anonymous]

CALCULATE(<your measures>,ALL(<yourtablename>))

This will ingore the row context and calculate measure on "All" the rows of "yourtablename"

Abhinav K
-----------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michael_knight]

Hi @Anonymous 

So do I do this and then use this measure instead of the measure I'm using in the coding in my original post?

Like so?

[All - C]/[C, DS, OA, OS, DP]

[Anonymous]

Well since I do not have any view of your data I shall try to help with formula

Lets say you want to calculate something A/B*C where A/B is grand total and C is monthly or vice versa

You need to use the formula = calculate(A/B, ALL(Table) ) * C

if you just want A to be part of grand total

then = calculate(A, ALL(Table) ) /B* C

Please note this has to be in a measure, which you can use for your vizualization

[michael_knight]

Cheers @Anonymous 

Tried all of them methods with no luck. I even tried to compare the measure with the ALL() function implemented into it with the one that doesn't have it and they're both exactly the same.