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

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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..

 

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!