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
sdukes
Helper I
Helper I

Dynamic Divide values by total of other column

I know this has been answered but I could not figure it out from previous posts...

how can I obtain the following:

CategoryValue1Value2Percent of totalexcel formula
Test1110.166667F2/G5
Test2220.333333F3/G5
Test3230.333333F4/G5
Total560.277778(average)

 

from this data set 

CategoryValue1Value2
Test111
Test211
Test211
Test311
Test301
Test311
Total56

 

I cannot figure out how to get the percent of total - everything I've tried has either given me a total per row, or a total of the entire Value2; unfiltered (imagine theres test4,test5, etc. that I'm filtering out)

 

I know it's a silly one, but any and all help is appreciated.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Write these measures:

Values1 = SUM(Data[Value1])
Values2 = SUM(Data[Value2])
Measure = AVERAGEX(VALUES(Data[Category]),[Values1]/CALCULATE([Values2],ALL(Data[Category])))
Hope this helps.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Write these measures:

Values1 = SUM(Data[Value1])
Values2 = SUM(Data[Value2])
Measure = AVERAGEX(VALUES(Data[Category]),[Values1]/CALCULATE([Values2],ALL(Data[Category])))
Hope this helps.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You got it! Thank you again Ashish

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
FrankAT
Community Champion
Community Champion

Hi @sdukes 

take a look at the following solution:

 

03-09-_2020_00-52-35.png

Percent of Total = 
IF (
    ISINSCOPE ( 'Table'[Category] ),
    DIVIDE ( [Sum of Value2], CALCULATE ( [Sum of Value2], ALL ( 'Table' ) ) ),
    AVERAGEX (
        'Table',
        DIVIDE ( [Sum of Value2], CALCULATE ( [Sum of Value2], ALL ( 'Table' ) ) )
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Greg_Deckler
Super User
Super User

@sdukes - Not entirely certain here:

Percent Total =
  DIVIDE(
    SUM([Value1]),
    SUMX(ALL('Table'),[Value2]),
    0
  )

That should be the individual rows. After that, you have a measures total problem, Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.