Measure vs Calculated Column

DAX DAX Calculations
1

I have a two column data points with weights (before and after). I want to calculate the average weight reduction (Column A-Column B)/Column A).

Some of the rows might have blanks or zeros. I want to filter so I am only calculating an average weight reduction for the rows that have information for BOTH.

I can do this with the following DAX equation as a calculated column:
PartWeightReduction =
IF(AND(‘Case Study Database’[ConventionalPartWeight]>0,‘Case Study Database’[iMFLUXPartWeight]>0), DIVIDE(‘Case Study Database’[ConventionalPartWeight]-‘Case Study Database’[iMFLUXPartWeight],‘Case Study Database’[ConventionalPartWeight]),BLANK())

Then I just tell the visual (card) to look at the average.

I am having trouble doing the same thing with a measure. I can get the individual values to be the same but I need to average to happen AFTER the filtered reduction calculation or it will be off. Can anyone help?

I tried this but I can’t get the average and the total that it shows is wrong:
PartWeightRed =
CALCULATE(
SUMX(‘Case Study Database’, DIVIDE(‘Case Study Database’[ConventionalPartWeight]-‘Case Study Database’[iMFLUXPartWeight],‘Case Study Database’[ConventionalPartWeight])),
FILTER(‘Case Study Database’,‘Case Study Database’[ConventionalPartWeight]>0),
FILTER(‘Case Study Database’,‘Case Study Database’[iMFLUXPartWeight]>0))

Averageissue
Averageissue1548×848 136 KB

2

if the problem is only in the total. Then leave your measure as it is, and then add another measure (the one you will use in the visual) that goes like this :

PartWeightRed 2 =
VAR SummrizedTabl =
    SUMMARIZE (
        'Case Study Database',
        'Case Study Database'[Ceated By.title],
        "Part weight", [PartWeightRed]
    )
RETURN
    IF (
        HASONEVALUE ( 'Case Study Database'[Ceated By.title] ),
        [PartWeightRed],
        AVERAGEX ( SummrizedTabl, [Part weight] )
    )
4 Likes
3

Thank you for helping!

Point of clarity: I need this total to be accurate regardless of “Created by.title”. I just have that in there to test the individual values. I need the total so that I can just put the correct Part Weight Reduction average on a card elsewhere in my report.

The main issue is the order (need part weight reduction first, of ONLY the items that have BOTH Conventional Part weight and iMFLUX part weight entries, then I need the average)

Does that make sense?

4

Firstly, for the part weight measure, if you put it in a card all by itself (without context) it will show you the correct average with the measure i’ve given you.
If you want to add it in a different matrix (with a different column), you will have to add that new context (column) to the measure (add it in the if statement with hasonevalue).
For the second part can you explain more what yu mean by order, because you can just sort your matrix by the colums or the measure

5

Hi @stiefel.ed, did the response provided by @HASSAN_BI help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi @stiefel.ed, we’ve noticed that no response has been received from you since the 15th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @stiefel.ed, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

8

My issue was that I was trying to use Sumx and then average after, rather than using the Averagex equation. This did what I needed it. Thank you.