cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

problem with multiple selection in sumx

I need a file where the selected data appears normally and other products appear grouped (classified as others). When the selection is unique, it works perfectly, but when the selection is multiple it does not work

 

To function it organizes a structure with 3 tables.
Base = Database
Auxiliar2 = GROUPBY (Base; Base [Cliente])
table2= UNION (GROUPBY (SELECTCOLUMNS (Base; "Cliente"; Base [Cliente]); Cliente]; GROUPBY (SELECTCOLUMNS (Base; "Cliente"; " Outros "); [Cliente]))

 

Auxiliar2 = Used for filter
table2 = used as the graph axis

 

 

mensure 2 =
IF (FILTROS (Tabela 2 [Cliente]) = FILTROS (Auxiliar2 [Cliente]);
SUMX (FILTER (Base; Base [Cliente] = FILTROS (Auxiliar2 [Cliente]); Base [Valor]);
IF (FILTROS (Table2 [Cliente]) = "Outros";
SUMX (FILTER (Base; Base [Cliente] <> FILTROS (Auxiliar2 [Cliente]); Base [Valor])))

 

Print 1 table.pngPrint 2 - filter.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: problem with multiple selection in sumx

Hi @LeandroCorrea

 

Please follow the link. It has the exact solution for you. I tried it on the pbix file you uploaded. It worked perfectly.

http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

 If it works for you pleasse accept this as solution and also give KUDOS.

Cheers

CheenuSing

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

9 REPLIES 9
Highlighted
Microsoft
Microsoft

Re: problem with multiple selection in sumx

Hi @LeandroCorrea,

I create sample table and try to reproduce your scenario. When the selection is unique, it still doesn't work. It only shows the selection value, the "Outros" is not shown as follows. If the slicer filter the x-axis, how to display "Outros"? There is related value in slicer. So please share your .pbix file for further analysis. 

1.PNG

My measure is shown below.

mensure 2 = 
IF (FILTERS( table2[Cliente]) = FILTERS(Auxiliar2[Cliente]),
SUMX (FILTER (Base, Base [Cliente] = FILTERS( Auxiliar2[Cliente])),Base[Valor]), 
IF (FILTERS( table2[Cliente]) = "Outros", 
SUMX (FILTER (Base, Base [Cliente] <> FILTERS(Auxiliar2[Cliente])), Base[Valor])))


And when the selection is multiple, there will be some values compared to some values in this part: FILTERS( table2[Cliente]) = FILTERS(Auxiliar2[Cliente]), the logic is wrong. So it didn't work correctly.

In addition, please use the following formula and check if it works fine.

Measure =
IF (
    SELECTEDVALUE ( Auxiliar2[Cliente] ) <> "Outros",
    SUMX (
        FILTER ( Base, Base[Cliente] = FILTERS ( Auxiliar2[Cliente] ) ),
        Base[Valor]
    ),
    SUMX (
        FILTER ( Base, Base[Cliente] <> FILTERS ( Auxiliar2[Cliente] ) ),
        Base[Valor]
    )
)


Best Regards,
Angelia

Highlighted
Frequent Visitor

Re: problem with multiple selection in sumx

how do I share the pbix file here?
Is there somewhere here on the page to attach or only by external link?

Highlighted
Microsoft
Microsoft

Re: problem with multiple selection in sumx

Hi @LeandroCorrea,

You can upload the .pbix file as attachments as follows.

1.PNG

Or you can store your .pbix file in onedrive, and post the share link.

2.PNG

Best Regards,
Angelia

Frequent Visitor

Re: problem with multiple selection in sumx

follows the file, as I had said, with simple selection works perfectly. It brings the selected customer into one column and groups the other clients into the "other" column of the chart. I need to select two clients, example (D and C) and the graph presents these three columns (D, C and one of Others)

 

Link: https://1drv.ms/u/s!AjuJi3nCDZmbhSpMIJVpuZPHR4eG

Highlighted
Microsoft
Microsoft

Re: problem with multiple selection in sumx

Hi @LeandroCorrea,

The difficult is how to change the part where the selection is multiple, there will be some values compared to some values in this part: FILTERS( table2[Cliente]) = FILTERS(Auxiliar2[Cliente])

Usually we use slicer to affect the chart directly. It affects measure in your chart. While use the slicer filter the x-axis directly, the Outros is not including in slicer, so it's hard to find a workaround. I will post the update if I find solution. 

Best Regards,
Angelia

Highlighted
Super User I
Super User I

Re: problem with multiple selection in sumx

Hi @LeandroCorrea

 

Please follow the link. It has the exact solution for you. I tried it on the pbix file you uploaded. It worked perfectly.

http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

 If it works for you pleasse accept this as solution and also give KUDOS.

Cheers

CheenuSing

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Highlighted
Frequent Visitor

Re: problem with multiple selection in sumx

Thank you my friend! excellent solution worked perfectly for what I needed.

I'm still newbie, but this type of measure is considered at what level?
intermediary or basic?

Highlighted
Super User I
Super User I

Re: problem with multiple selection in sumx

Hi @LeandroCorrea

 

I would say intermediary.

 

Good to hear your problem was solved.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Frequent Visitor

Re: problem with multiple selection in sumx

@v-huizhn-msft and @CheenuSing, thanks for the help! Have a great weekend

Sorry, I do not speak English very well.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors