Error in calculating % grand and % sub totals. Need to calculate GWP (sales) for Name and Program

[Anonymous]

Hi,

Hope you're doing well 🙂

I'm stuck on calculating the % grand or % subtotals basically I want to calculate the % for GWP for Name and Program.

I've created two measures :

% of GWP Partner = sum(WrittenPremiumAggregate[GrossWrittenPremium])/CALCULATE(sum(WrittenPremiumAggregate[GrossWrittenPremium]),ALLSELECTED(MGAList[MGADetailName]))

% of GWP Program = sum(WrittenPremiumAggregate[GrossWrittenPremium])/CALCULATE(sum(WrittenPremiumAggregate[GrossWrittenPremium]),AllSelected(Programs[ProgramDetailName]))

They're working correctly but I don't want two-measure I want only a single measure that will give me the % total. 

Attached below are the sample data and the pbix file. 

https://drive.google.com/file/d/1g0ljZMa5RMlkpBUsQMuPlWJ6D_vlm9uU/view?usp=sharing

will highly appreciate it if anyone can help me with this.

Thanks!

[lbendlin]

I don't understand what your expected outcome is but be aware that Power BI (like Excel) has some rudimentary implicit measures for these scenarios

See attached for an example.

[Anonymous]

Hi Ibendlin,

I know that I can use this to get my desired output but I want to create a separate measure for this.

I've already calculated the grand totals with this measure :

test1=

DIVIDE (

SUM ( WrittenPremiumAggregate[GrossWrittenPremium] ),

CALCULATE (

SUM ( WrittenPremiumAggregate[GrossWrittenPremium] ),

ALLSELECTED (MGAList[MGADetailName] ),

ALLSELECTED ( Programs[ProgramDetailName] )

)

)

I want to see this as the output :

For Mga Name - Indiviual mga gwp / total gwp
Ex-

Balance Partners (058) -> 64116586.06219995/ 1817772703.3361006 (total ) = 3.53%

For ProgramDetailName -> 

Ex-
Balance Partners (058) -> Balance Partners Aspyre (ADM) (145) ->109188

Balance Partners (058) -> Balance Partners Pentium Property (144) ->27404951

Balance Partners (058) -> Balance Partners Pentium Property (ADM) (143) ->36602446

so the calculate should be -Balance Partners Aspyre (ADM) (145)->
                                            109188 / 1091886+27404951+36602446 = 0.167%

                                            Balance Partners Pentium Property (144) ->

                                            27404951/ 1091886+27404951+36602446 = 42.09%

                                             Balance Partners Pentium Property (ADM) (143) ->

                                             36602446 / 65099283 = 56.22%

I've tried all the possible functions isinscope,has one value for the hierarchy calculation but I'm unable to get this output. 

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Please add another formula as below:

test2 = 
IF (
    ISINSCOPE ( Programs[ProgramDetailName] ),
    WrittenPremiumAggregate[% of GWP Program],
    WrittenPremiumAggregate[% of GWP Partner]
)

Result:

By the way, it seems that you have miscalculated the result.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @v-easonf-msft ,

Thank you for the solution it works perfectly fine.