Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.