Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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()))
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
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 |
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
Product | Measure sum of sales |
A | 300 |
B | 200 |
C | 50 |
D | 100 |
that's the output table, can you share how does the input table look like? Also can you transform it if needed?
hi, please find bellow the table.
Project ID | Sales 2020 | Sales 2021 | Sales 2022 | Sales 2023 | Sales 2024 | Sales 2025 |
20814 | 25.59 | 30.64 | 35.83 | 35.83 | 35.83 | 35.83 |
9941 | 23.75 | 24.7 | 26.84 | 28.98 | 31.11 | 33.25 |
8102 | 16.39 | 16.39 | 16.39 | 16.39 | 16.39 | 16.39 |
17097 | 12.67 | 14.79 | 17.26 | 17.43 | 16.44 | 16.85 |
15202 | 6.58 | 7.58 | 7.1 | 7.1 | 7.1 | 7.1 |
16142 | 6.09 | 6.18 | 6.27 | 6.37 | 6.46 | 6.56 |
17769 | 5.14 | 5.09 | 5.06 | 5.02 | 4.99 | 4.96 |
15310 | 4.64 | 4.64 | 4.64 | 4.64 | 4.64 | 4.64 |
18334 | 4.45 | 4.6 | 4.74 | 4.88 | 5.02 | 5.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:
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()))
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!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |