Dynamic summation according to the visual context

[Rai_BI]

Dynamic summation according to the visual context

Hello friends,

In Power BI I have a table with two columns. The 'Seller1' column and the 'Seller2' column.

I intend to create three table visuals in which visual 1 will have the Seller1 column in context. In visual 2 you will have the Seller2 column in context and in visual 3 you will have the Seller1 and Seller2 column in context.

I need to create a single measure for the three visuals in which if the context contains the column Seller1 then return the measure [Goal 1] otherwise if the context contains the column Seller2 then return the measure [Goal 2] otherwise if the context contains the column Seller1 and also the Seller2 column then return the sum of the measures [Goal 1]+[Goal 2].

I made the following DAX measure below, but it does not correctly calculate the sum of the two measures [Meta1]+[Meta2] when the two columns are in the visual context.

DAX measurement...

Single Measure =
IF(
HASONEFILTER('customer table'[ID Seller1]),
[Measure 1],

IF(
HASONEFILTER('customer table'[ID Seller2]),
[Measure 2],

[Goal 1]+[Goal 2]
)
)

 I've attached an example pbix file so you can better understand what I'm looking for. Download

[123abc]

To achieve your desired outcome in Power BI with a single measure, you can use the following DAX expression:

Single Measure =
VAR IsSeller1Selected = HASONEVALUE('customer table'[Seller1])
VAR IsSeller2Selected = HASONEVALUE('customer table'[Seller2])
RETURN
IF(
IsSeller1Selected && NOT IsSeller2Selected,
[Goal 1],
IF(
IsSeller2Selected && NOT IsSeller1Selected,
[Goal 2],
IF(
IsSeller1Selected && IsSeller2Selected,
[Goal 1] + [Goal 2],
BLANK()
)
)
)

Explanation:

• VAR statements are used to define variables to check if only Seller1 or Seller2 is selected in the context.
• The RETURN statement uses nested IF conditions to evaluate the context.
• If only Seller1 is selected, it returns [Goal 1].
• If only Seller2 is selected, it returns [Goal 2].
• If both Seller1 and Seller2 are selected, it returns the sum of [Goal 1] and [Goal 2].
• If none of the conditions are met, it returns BLANK().

Make sure to replace 'customer table', 'Seller1', 'Seller2', '[Goal 1]', and '[Goal 2]' with the actual names used in your Power BI model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Rai_BI]

Hi, thanks for your help.

Your measure is working as mine, it does not correctly calculate the sum of the two measures [Goal 1]+[Goal 2] when the two columns are in the visual context and the grid total is returning blank.