Thank You for posting your query onto the Forum.
Well the problem is with the data. When I analyzed the data into the Power Query I came to know where the problem has actually occurred and why the results were errorneous.
Let’s go through the step-by-step to understand where the problem actually was and why we’re not getting the results as expected.
Step 1: Errorneous/Ambiguous Results In Table Visual
In the provided screenshots below, you’ll observe that although “Cost Maintained” results are correct but “Cost Increased and Cost Decreased” results are errorneous inspite of “Cost Change (in $) = 0”.
So after observing this incorrect results where did the error lie? And the answer is - In the Data itself and not in the formulas.
Step 2: Go back to the Power Query
Now, the data type for columns Old Product Cost and New Product Cost is by default selected as “Decimal Number”. So when we click onto the filter drop-down option of both the columns. The numbers/data actually looks like this. Below is the screenshot provided for the reference -
Now, after observing the above screenshots you must be thinking that let’s change the data type of these two columns from “Decimal Number” to “Fixed Decimal Number”. Let’s try this and see how it looks in the next step.
Step 3: Change the data type of Old Product Cost and New Product Cost from “Decimal Type” to “Fixed Decimal Number”.
Now, since we have changed the data type from “Decimal Number” to “Fixed Decimal Number”. Let’s check whether the numbers are reduced to fixed number of decimal places or not. Below are the screenshots provided for the reference -
So now, you’ll observe that although the numbers have been reduced from recurring to a fixed decimal number but still there’s a problem. And that is, the decimal places under the Old Product Cost have been reduced either upto 3 or 4 places whereas for the New Product Cost the same have been reduced upto 1 or 2 places. So still there’s an “inconsistency” between the both the columns in terms of decimal places which will again provide the errorneous/ambiguous results. Let’s see what can be the concrete solution in the next step.
Step 4: Round the numbers upto 2 Decimal Places.
Rather than converting the data type from “Decimal Numbers” to “Fixed Decimal Numbers”. Round the numbers upto the two decimal places. Below is the screenshot provided about how to do it.
Select both the columns i.e. Old Product Cost and New Product Cost and go the “Transform” tab under the Power Query.
Under that from the “Number Column” menu click on the “Rounding” option and select the “Round…” option from the drop-down list and mention the decimal places option as 2. Here the screenshot -
Click on the “Close and Apply” option in order to check the final results of the “Table Visual”
Step 5: Final Results.
So now you’ll observe that wherever the “Cost Change = $0” it’ll be shown under the category “Cost Maintained” and not under the other categories which was showing earlier. And therefore, by this was we will also have correct results for the “Cost Increased and Cost Decreased” categories.
Finally, before I sign off from this post it’s still not understandable to me about why you’re trying to use the “Grouping and Segmentation Technique” when it can done using the simple formulas and techniques.
In order to show these 3 categories you can either simply create a “Calculated Column” or create a “Measure”. Below are the formula’s provided for the reference
Formula for creating a measure -
Cost Status =
SWITCH( TRUE() ,
[Cost Change (in $)] < 0 , "Cost Decreased" ,
[Cost Change (in $)] = 0 , "Cost Maintained" ,
[Cost Change (in $)] > 0 , "Cost Increased" ,
Formula for creating a calculated column -
Cost Difference =
SWITCH( TRUE() ,
( CALCULATE( [Total New Product Cost] ) - CALCULATE( [Total Old Product Cost] ) ) > 0 , "Cost Increased" ,
( CALCULATE( [Total New Product Cost] ) - CALCULATE( [Total Old Product Cost] ) ) = 0 , "Cost Maintained" ,
( CALCULATE( [Total New Product Cost] ) - CALCULATE( [Total Old Product Cost] ) ) < 0 , "Cost Decreased" ,
Note: The reason behind why I created a “Calculated Column” is that I can filter out the results since we cannot place measures under the “Slicers”. So conclusion is out both of these options you can simply create a “Calculated Column” and put it under the “Table Visual” as well as under the “Slicers” there’s no need to create both of them.
I’m also attaching the PBIX file of the working for the reference.
Hoping you’ll find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Dynamic Grouping - Harsh.pbix (153.3 KB)