Forum Discussion
Absolute at total
- Anonymous9 years ago
Omega,
Create the following measures and check if you get expected result.
NewMeasure = IF(COUNTROWS(VALUES(Table2[SKU]))=1,Table2[ABSDIFF],SUMX(VALUES(Table2[SKU]),[ABSDIFF]))
Measure 3 = [NewMeasure]/[ExportTotal]
Regards,
Lydia
Same values as I had :(
divide(SUMX(Table2,[Measure]),calculate(SUMX(Table2,Table2[Export]),sku = 8510))
- Omega9 years agoImpactful Individual
What if I have multiple SKUs?
- vanessafvg9 years agoCommunity Champion
divide(SUMX(Table2,[Measure]),calculate(SUMX(Table2,Table2[Export]), filter(table2, sku = 8510 && sku = 111))
- Omega9 years agoImpactful Individual
Thanks but again, this will not solve the problem. Assume that I have one million SKUs, I won't be able to type all 1 million SKUs :(
- vanessafvg9 years agoCommunity Champion
what is your actual requriement here?
you want to divide it into some of the skus but not all?
can you tell me what your end goal is, ie the business requirement
- Omega9 years agoImpactful Individual
Requirement: Calculate Abs(Export - Import)/Export at SKU level, week level and month level assuming month level have been already defined based on the weeks.
We have many SKUs and it's impossible to filter them. We need a general measure that does the calculation regardless of the numbers of SKUs.
Thanks!
- vanessafvg9 years agoCommunity Champion
alright i am just trying to understand the problem
do you want all the sku's or just certain sku's, so what i am saying how will power bi know what sku's to use? is there an indicator that is applied to them. the ones you need to use. what is the condition that will include them in the calculation
- Omega9 years agoImpactful Individual
It should be all SKUs.
Imagine in excel::
You have a list of SKUs that have exports and imports in different weeks (Same as the one I shared in my first post but with multiple SKUs). You will create a column that will get you the absolute difference for each SKU which is the measure we have created earlier. At the end, you will sum up the exports and the absolute diffenence for all SKUs and then you will will divide the summation of absolute difference to the total exports to get a percantage. This percentage will be at SKU level.
So in my example, 470/8510 = 5% for that SKU.
I hope I'm clear.