Avoid adapting a measure in a table, and IF() returning too many rows

[Twelve]

Hi everyone,

Here's a WeTransfer for the PBI in case you want to help me: https://we.tl/t-sfdijb0Q4Y

I have 3 tables, linked together in the follwoing way:

So far, for a selected Category (selected with a slicer on the category's name), I display all the information that I have in the Table Feuil2 and the sum of Value from Feuil3. In addition, I computed a weighted average of the Value for each attribute in the selected category, weighted by their sales. For this, I used the following DAX formula:

average_value_weighted_by_sales =
DIVIDE( 
    SUMX(Feuil3,
         Feuil3[Value] * CALCULATE(SUM(Feuil2[AttributeSales]))
    ),
    CALCULATE(SUM(Feuil2[AttributeSales]))
)

My Power BI looks like that:

What I would like to do it add a column to the Table visual that would take the value "OK" if the sum of an attribute's value is smaller than the displayed average_value_weighted_by_sales, or "KO" if the sum is greater.

In my example, that'd mean that I'd have "KO" for the first row (since 1.94383 > 1.81081), "OK" for the second row (since 1.51867 <= 1.81081), and "KO" for the last row (since 2.2050 > 1.81081).

Of course, I would need this to be dynamic when we select a new category.

While doing to do this, I have faced two problems:

1. The measure that I created adapts to the context (I guess that's the name?), meaning that if for an attribute I ask how its value compares to the measure, it's always equal since the measure wil be computed based on the attribute only. We can see that by placing the measure in the visual and we will see that the columns Value and average_value_weighted_by_sales will be equal.
2. Th function IF (which I think is the next step) returns all the rows and not only the ones I filtered with the slicer.
   

Thank you for you help!

[tamerj1]

Hi @Twelve 
Here is the sample file with the solution https://www.dropbox.com/t/6EOcCJOeKP6UIZOu

Just adjust the formula of [average_value_weighted_by_sales] by wrapping it with CALCULATE to apply the required modifiers as follows:

average_value_weighted_by_sales = 
CALCULATE (
    DIVIDE ( 
        SUMX ( Feuil3,
            Feuil3[Value] * CALCULATE ( SUM ( Feuil2[AttributeSales] ) )
        ),
        CALCULATE ( SUM( Feuil2[AttributeSales] ) )
    ),
    REMOVEFILTERS (),
    VALUES ( Feuil1[CategoryName] )
)

Then you can use it in your IF statement. 

Please note that you need to use the matrix visual as the filter context in the table visual would be too complicated to handle. Further I had to create a filter measure which is hidden inside the matrix just to force it not to hide blanks. I guess you can do that throughout some settings as well but I personally prefer playing with DAX.
Please let me know if you have any question.

Note: the table names you've been using in the file were confusing to me therefore, I used different names inside the file. Let me know if you need the same with the old names.

[Twelve]

Hello @tamerj1 ,

Thank you very much for your answer.
I wrapped my original formula in a CALCULATE and added the modifiers and it works fine unless I want to select two categories at the same time:

Do you know how we can allow several categories to be selected? The same problem occurs if I filter something else than the categories.

Regarding the IF statement, I don't want to return the rows that are not in the selected categories, so I re-wrote your formula to the following:

Mesure = 
IF ( 
    ISBLANK ( [average_value_weighted_by_sales] ),
    BLANK(),
    IF ( 
        [ContValue] <= [average_value_weighted_by_sales],
        "OK",
        "KO"
    )
)

 
Thank you in advance!

[tamerj1 · Accepted solution]

Hi @Twelve 
This one works for any selection

average_value_weighted_by_sales = 
DIVIDE (
    SUMX ( 
        ALL ( Sales[ID], Sales[Name] ),
        CALCULATE ( 
            SUMX (
                Context,
                Context[Value] * CALCULATE ( SUM ( Sales[Sales] ) )
            )
        )
    ),
    SUMX (
        ALL ( Sales[ID], Sales[Name] ),
        CALCULATE ( 
            SUM ( Sales[Sales] )
        )
    )
)

[tamerj1]

@Twelve 
Just noticed that you need to disply all "Names". Please redownload the file https://www.dropbox.com/t/VmDYVcL9fFwhdV57

[amitchandak]

@Twelve
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Twelve]

Hello @amitchandak,

There was a a link to a WeTransfer in my post. Here it is again, let me know if it doesn't work: link tp sample pbix.

Thank you