cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route11 Member
Member

filtering and adding back the value to another item in the filter

Hi Experts

 

The filter is made up of product categories...

 

I have a filter which at visual or page level i can unselect a particular product that i do not want to show, but i want to then add back the value of the filtered item to another product category. so the overall total reamains the same.

 

i.e. remove from one product add to another....is this possible in BI

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: filtering and adding back the value to another item in the filter

Hi @Route11,

 

Try the following 2 measures:

 

Sales Pears =
VAR ProductToExclude = "Pears"
VAR CompanyToJoin = "ID"
VAR TotalSales =
    SUM ( Sales[Sales] )
VAR SelectProducts =
    SELECTEDVALUE ( Sales[Product] )
VAR SelectCompany =
    SELECTEDVALUE ( Sales[Company] )
RETURN
    IF (
        SelectProducts = ProductToExclude
            && SelectCompany <> CompanyToJoin;
        BLANK ();
        IF (
            SelectProducts = ProductToExclude
                && SelectCompany = CompanyToJoin;
            CALCULATE (
                SUM ( Sales[Sales] );
                ALL ( Sales );
                Sales[Product] = ProductToExclude
            );
            TotalSales
        )
    )




Sales of Pears in company ID = 
        IF (
            HASONEVALUE ( Sales[Company] );
            SUMX ( Sales; [Sales Pears]);
            [Sales Pears]
        )

 

Should work as intended.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

Re: filtering and adding back the value to another item in the filter

Hi @Route11,

 

If you use a measure you can do it, however witthout any sample data is difficult to give you an answer.

 

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Route11 Member
Member

Re: filtering and adding back the value to another item in the filter

Picture1.pngHi MFlex

 

The problem is always uploading and or providing data.... i fully understand that post and agree that is the best method. See sample data. So what we have is Co's ABC and BCD so on...they all sell products as shown the companies and products are in Hierarchires in the source data. i want to dynamicall remove pear form ABC and add these values back to Pears in Co. ID..

 

 

Super User
Super User

Re: filtering and adding back the value to another item in the filter

Hi @Route11,

 

Try the following 2 measures:

 

Sales Pears =
VAR ProductToExclude = "Pears"
VAR CompanyToJoin = "ID"
VAR TotalSales =
    SUM ( Sales[Sales] )
VAR SelectProducts =
    SELECTEDVALUE ( Sales[Product] )
VAR SelectCompany =
    SELECTEDVALUE ( Sales[Company] )
RETURN
    IF (
        SelectProducts = ProductToExclude
            && SelectCompany <> CompanyToJoin;
        BLANK ();
        IF (
            SelectProducts = ProductToExclude
                && SelectCompany = CompanyToJoin;
            CALCULATE (
                SUM ( Sales[Sales] );
                ALL ( Sales );
                Sales[Product] = ProductToExclude
            );
            TotalSales
        )
    )




Sales of Pears in company ID = 
        IF (
            HASONEVALUE ( Sales[Company] );
            SUMX ( Sales; [Sales Pears]);
            [Sales Pears]
        )

 

Should work as intended.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!