Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear Power BI users,
I have writed a dax code/measure where I subtracted column A from B, that results in C. Now I would like to sum up only the values that are greather than zero >0 using the dax measure C. In the picture below you can see the positives and the negatives. Please help me with making a measure for this, no calculated columns please.
My measure C is:
Measure C looks like this: Itemnr Price difference COGS
1 20
2 10
3 0
4 -5
How can i use my current measure "Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]" to write a new measure that filters out everything above 0 that would result in 30 and the other way around (other measure) that should result in -5?
Your sincerely,
Baris
Solved! Go to Solution.
@Anonymous ,
Try it:
Hi @Anonymous ,
You can create this measure using SUMX, for example:
X = SUMX(FILTER(TABLE; COLUMN_X > 0); COLUMN_A - COLUMN_B)
You can check on this link: https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-power-bi-and-power-pivot/sumx-summary-with-filter-or-additional-calculation-dax-power-pivot-power-bi/
Ricardo
Hi @camargos88 ,
Thank you for your reply, but you example is valid when there is a column_x in the table. Instead of creating a column_x i have created a measure mentioned above, this was "
Hi @camargos88
That would be great. I have prepared the PBI file in an anonymous way. See: https://1drv.ms/u/s!ApwLzcPKGlcdhU32GNVPjFNS4f8s?e=nuAlEZ
The measure which I use are included. Please use the measure mentioned before this was "Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]". I have also added a visual on the right side. If it works correct I would like to create a measure thats sums 17288 + 12709 and another measure that sums -69584+-82624. I am aware that I can select this in the visual, but I do not want to that. Thank you in advance Ricardo!.
Yours sincerely,
Baris
Hi @Anonymous ,
I didn't get your point. What do you mean by :
"If it works correct I would like to create a measure thats sums 17288 + 12709 and another measure that sums -69584+-82624. I am aware that I can select this in the visual, but I do not want to that" ??
Also, I see you are using sumx with those 2 measures, do you wanna filter the measure value > 0 ? If yes, which one ?
Ricardo
Hi @camargos88
I would like to create a measure value >0 using the measure "
Hi,
Assume that [Old cogs x current demand] and [New cogs x current demand] comes from different fact tables, how are we going to use the measure below to sum up everyhting above > 0.
Price difference COGS = SUMX(FILTER('Fact table'; [Old cogs x current demand]-[New cogs x current demand] > 0); [Old cogs x current demand]-[New cogs x current demand])
Yours sincerely,
Baris
I think we are almost there, because now I would like to see the result of that two itemnr toghether (thus, summed up). When we use blank() it does not show the total sum of the two items.
Yours sincerely,
Baris
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |