cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anany
Helper I
Helper I

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

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!

 

 

 

Anany_0-1652811687720.png

 

1 ACCEPTED SOLUTION

Hi, @Anany 

Please add another formula as below:

 

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

 

Result:

veasonfmsft_1-1653036455778.png

 

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

veasonfmsft_0-1653035276801.png

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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

lbendlin_0-1652912703407.png

 

See attached for an example.

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. 
 
 
 

 

Anany_0-1652922619491.png

 

Hi, @Anany 

Please add another formula as below:

 

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

 

Result:

veasonfmsft_1-1653036455778.png

 

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

veasonfmsft_0-1653035276801.png

Best Regards,
Community Support Team _ Eason

Hi @v-easonf-msft ,

 

Thank you for the solution it works perfectly fine. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors