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.
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:
Thank you for you help!
Solved! Go to 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] )
)
)
)
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.
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!
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] )
)
)
)
@Twelve
Just noticed that you need to disply all "Names". Please redownload the file https://www.dropbox.com/t/VmDYVcL9fFwhdV57
@Twelve
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
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 |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |