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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Creating 100% totals on row and columns for a measure that is a percentage itself

Hello everyone,

 

I am working on a matrix which contains a measure that's a % calculation using two other measures.

% Funded = DIVIDE([Approved apps],[Total Apps])

 

I then created a matrix of the [% Funded] based on Purpose( in rows) and CreditLevel (in columns) resuting in below matrix format  (subtotals and grand totals removed):

 

PurposeSubPrimePrime5Prime6Superprime
Auto5%3.4%8%5%
DebtConsol4.6%4.8%6%10%
HomeImprovement8%3.5%3.8%4.6%
Others9.4%9%3.4%8%

 

What I am looking for is that the above %s to change such that I have the row total percentage and column total percentage to be 100% each from the above %values. 

 

I do know if the cell values were numbers ( eg: [Total apps]), I could have used a DAX formula given below for column totals to end up 100%, but its not working for a measure that's a percentage itself:

 
1
2
3
4
5
6
% of CreditTier on Rows =
 
DIVIDE (
[Total Apps],
CALCULATE ( [Total Apps], ALLSELECTED( 'Table'[Purpose] ) )
)

 

Please do let me know if I need to add any more info to resolve this.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Check

% Brand = 
CALCULATE ( SUM ( Sales[Sales] ) )
    / CALCULATE (
        SUM ( Sales[Sales] ),
        ALLEXCEPT ( 'Item','Item'[Brand])
    )*100

Screenshot 2019-09-12 23.43.26.png

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

I have a report listing invoices with multiple sales. The invoice is listed 3 times and the tax lines are the same so I have a column:

*Duplicated = COUNTROWS(FILTER('Data Set','Data Set'[Invoice #] = EARLIER('Data Set'[Invoice #]))) to count the number of times the invoice is duplicated.
I need to divide the tax by the number of times the invoice is duplicated but I need the total for the 3 lines to equal 100% of the total tax.
In this case the total tax is $5.90 but the equation comes out totaling $5.91 so I need one line $.01 less than the other two to calculate the correct tax.
 
Any suggestions??
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

The info you gived is not complete, could you please also share measures [Approved apps], [Total Apps]. If possible, please also give the expected output.

 

Regards,

Jimmy Tao

Anonymous
Not applicable

[Approved] := calculate(distinctcount(AppdId),FILTER(distinct(Data[SubmittedDate]),
[SubmittedDate] >= MIN(dCalender[DimDateKey]) && [SubmittedDate] <= MAX(dCalender[DimDateKey]) && [IsApproved] =1))

 

[Total Apps] := calculate(distinctcount(AppdId),FILTER(distinct(Data[SubmittedDate]),
[SubmittedDate] >= MIN(dCalender[DimDateKey]) && [SubmittedDate] <= MAX(dCalender[DimDateKey])))

 

Expected output matrix:

PurposeSubPrimePrime5Prime6SuperprimeExpected Total
Auto20%40%32%8%100%
DebtConsol15%35%20%30%100%
HomeImprovement10%30%40%20%100%
Others40%25%30%5%100%

 

So, if the total count of Funded (since the intial % was for funded) is 100; suppose, 25 of them are Auto ( as purpose), then this 25 is spilt into the four credit levels ( eg: #Subprime - 5, #Prime5 - 10, #Prime6 - 8 and #Superprime: 2, which totals 25). So, the row totals have to end up to 100% ( i.e. Subprime: 5/25 = 20%, Prime5: 10/25 = 40%, Prime6: 8/25 =32%, Superprime: 2/25 =8%).

 

I hope I was able to explain it. Similarly, I want to do the same at the column level as well.

 

Check

% Brand = 
CALCULATE ( SUM ( Sales[Sales] ) )
    / CALCULATE (
        SUM ( Sales[Sales] ),
        ALLEXCEPT ( 'Item','Item'[Brand])
    )*100

Screenshot 2019-09-12 23.43.26.png

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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