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
Vandergledison
Helper III
Helper III

new % measure refering to another measure

Hi everyone. I created a measure that calulates the SUMX of the sales of products in the last 10 years. Each product is in a singular row. Now i want to simply create the respective % of this values. when i try to use the previous measure to sum all the products it doesnt work. it calls for a Column and the measure doesnt show in the availible menu. any tip in how to create a measure based on other measure? thanks

1 ACCEPTED SOLUTION

Try something like this:

Measure = DIVIDE([Measure sum of sales],CALCULATE([Measure sum of sales],ALLSELECTED()))

Product is not in the input table you posted, so I cannot guarantee it will work, you would need to post all related tables

 

Also I would suggest changing your sales table using Unpivot, like this:

Capture.PNG

or to make it even better remove 'Sales ' from the Attribute column, so that you could use range filters. It makes the maintenance much easier (data for a new year is just new rows), you could then use following measures, which will be faster (although that may not be noticable with a small dataset)

Sales = SUM(Sales[Value])

Sales 2025 = CALCULATE([Sales], Sales[Attribute]=2025)

Sales 2020-2025 = CALCULATE([Sales],Sales[Attribute]>=2020,Sales[Attribute]<=2025)

Ratio = DIVIDE([Sales],CALCULATE([Sales],ALLSELECTED()))

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Please do yourself a favor and before you start creating unmanageable and franken-models nobody will want to touch, learn how to do it the right way:

https://www.youtube.com/watch?v=78d6mwR8GtA
https://www.youtube.com/watch?v=_quTwyvDfG0

Most importantly, learn about star schemas and storing data efficiently. If you don't do it, your DAX will not only be nightmarishly complex. It will be agonizingly slow.

Best
D

Hi, thanks a lot for the links and i will definetly take a look.

i do understand how the stars schemes works and how to create Dim tables  containing the keys to a Dataset table.

im just starting with the "Measures" (before i was creating simple columns, but started to get too big).

My question with this post was basically if i could use a measure to refer to previous measures. i just need to know what kind of formulas im allowed to use with measures.

thanks

Stachu
Community Champion
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1 Column2
A 1
B 2.5

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Sure, i use the DAX bellow to calculate colum B

Measure sum of sales = SUMX(Sales,Sales[2020]+Sales[2021]+Sales[2022]+Sales[2023]+Sales[2024]+Sales[2025]+Sales[2026]+Sales[2027]+Sales[2028]+Sales[2029]+Sales[2030])

now what i would need is to create a new Measure to calcualte the previous Measure but in % of the total sales.

thanks

ProductMeasure sum of sales
A300
B200
C50
D100

that's the output table, can you share how does the input table look like? Also can you transform it if needed?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

hi, please find bellow the table.

 

Project
ID
Sales 2020Sales 2021Sales 2022Sales 2023Sales 2024Sales 2025
2081425.5930.6435.8335.8335.8335.83
994123.7524.726.8428.9831.1133.25
810216.3916.3916.3916.3916.3916.39
1709712.6714.7917.2617.4316.4416.85
152026.587.587.17.17.17.1
161426.096.186.276.376.466.56
177695.145.095.065.024.994.96
153104.644.644.644.644.644.64
183344.454.64.744.885.025.18

I created a SUMX measure for 2020-2025 and now i need a % for each product based on the total of this 5 years sales

thanks

Try something like this:

Measure = DIVIDE([Measure sum of sales],CALCULATE([Measure sum of sales],ALLSELECTED()))

Product is not in the input table you posted, so I cannot guarantee it will work, you would need to post all related tables

 

Also I would suggest changing your sales table using Unpivot, like this:

Capture.PNG

or to make it even better remove 'Sales ' from the Attribute column, so that you could use range filters. It makes the maintenance much easier (data for a new year is just new rows), you could then use following measures, which will be faster (although that may not be noticable with a small dataset)

Sales = SUM(Sales[Value])

Sales 2025 = CALCULATE([Sales], Sales[Attribute]=2025)

Sales 2020-2025 = CALCULATE([Sales],Sales[Attribute]>=2020,Sales[Attribute]<=2025)

Ratio = DIVIDE([Sales],CALCULATE([Sales],ALLSELECTED()))

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

After re-organizing the DATA i ended up with a similar problem. now i want to calculate the percentile.inc of the %sales measure and also shows only columns availible. i just want to rank each product like, if the %sales is higher than 80% of the total, rank is 5, if its higher than 60%, rank4, if its higher than 40% is 3..and so on.

on excel, i can do it by combining if and the percentile.inc function. just need to understand how it works in power BI.

cheers

Hi, it worked like a charm.

normally i do unpivott the year columns but in this example i was firtsly checking the possibility of creating a score algorithm containing different inputs like sales, CM1, risk, etc. Working for sales, i will then do it properly..i hope.

just one thing to understand the mechanics, you added DIVIDE function which is easy to understand, but on the denominator you added "CALCULATE" and then the previous measure without "SUM". is Calculate already suposed to sum all the rows in that column?

thanks a lot for your support!

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.

Top Solution Authors