Subtotal for measure with selected value

[Anonymous]

Hi,

I am calculating from respones of a questionaire. I calulate from two tables and want to subtotal values, which a measure calculated for each column:

Proportion =

SUM('Shiptype'[How many]) * SELECTEDVALUE('Category Percentage'[Factor])

Responses are transformed to a factor and then sumed.

The measure Proportion calculates the Fraction of the total Shiptypes. And in the visual I get a row total but I need a column subtotal?

Any input is welcome. I spend one day on this and cant crack the nut

Cheers

[Anonymous]

Hey Hasham,

Thank you for your reply. I understand your approach, but it doesnt work. I have impelemented it and called Proportion2:

Proportion2 =

SUM('Shiptype'[How many ships are your answers above based on?])

*

if (SELECTEDVALUE('Category Percentage'[Factor])>1, 0, SELECTEDVALUE('Category Percentage'[Factor]))

I cirquled the problem. If Factor occurs only one time I get a Subtotal, but as soon as it occurs multiple times I do not get a subtotal. 

See row 8h

I have thought about creating a list with zeros if selectedvalue(...[Factor]) is empty. But does not work...

Thanks for your time!

[HashamNiaz]

Hi @Anonymous !

You are not getting [Proportion] measure or Total Shiptype value on your matrix because of following reason;

Your measure uses a SELECTEDVALUE('Category Percentage'[Factor]), while in the case of individual Category it's able to calculate the [Factor], but when you are at Column SubTotal, SELECTEDVALUE() has all the categories & unable to identify which [Factor] to use, you can try to nest it inside IF() as shown below;

IF(SELECTEDVALUE() > 1, Use Some other Value, SELECTEDVALUE())

You need to supply the value, what you want to use when SELECTEDVALUE() > 1

Regards,

Hasham