Total of a measure without column fields

[Anonymous]

Hello

I am carrying out some analysis and I will show a summarised example of the report view in the table below:

Index	Status	Amount	Amount Total
1	Provided		[OPEN]
2	Not provided	1	[OPEN]
3	Not provided	8	[OPEN]
4	Not provided	9	[OPEN]
5	Provided		[OPEN]
6	Not provided	10	[OPEN]
		28	[OPEN]

All the items except the index above, are stored in measures. I have created a measure to show the amount only for items that are "not provided".

Someone please advise how I can the fill the Amount Total measure to give the amount total in all records for "Not provided", i.e 28 in the example. The ultimate goal is to do a percentage of total ([Amount]/ [Amount Total])

I can do this if it was columns using the ALL and SELECTEDVALUE functions, but I am coming up stuck trying to deduce the calculation since pretty much all processes leading to this are measures.

[v-yanjiang-msft]

Hi @Anonymous ,

You can modify the formula to: IF( ISINSCOPE ('Table'[Index]), <origianal formula>, <formula for the total value>)

I give an example to clarify:

Create a measure:

Measure =
IF (
    ISINSCOPE ( 'Table'[Index] ),
    MAX ( 'Table'[Sales] ),
    SUM ( 'Table'[Sales] )
)

Get the result:

Best regards,

Community Support Team_yanjiang

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

[Anonymous]

Hi @v-yanjiang-msft 

Thank you for this. However, the formula seems to work on columns. Is there a way to have this work on measures?

For example, using your response, the 'Sales' column in my model is a measure. What I require is a sum in a new measure, that will fill all records with the exact sum in the report view. So each index to show 28. As far as I can tell, intellisense does not pick measures to proceed with the formula.

Just for the record I re-worked my model with calculated columns and achieved my goal with a measure referencing these columns. So this here is just for knowledge purposes.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

My sample is only an example to clarify the use of formula: IF( ISINSCOPE ('Table'[Index]), <original formula>, <formula for the total value>)

This is a way for the totals of measure, columns will automatically sum the total. <oridinal formula> here is your measure, <formula for the total value> here is the specific formula for the total value of measure, I should know the specific sample to provide a solution, it's different in samples.

Best regards,

Community Support Team_yanjiang

[Anonymous]

Great!

I understood and modified the formula to have <formula for the total value> to be SUMX(Table, [measure]) and this worked.

Brilliant work mate.