Forum Discussion

LeandroCorrea's avatar
LeandroCorrea
Frequent Visitor
8 years ago
Solved

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])))

 

 

Need Help

9 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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. 



    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

    • LeandroCorrea's avatar
      LeandroCorrea
      Frequent Visitor
      8 years ago

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

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi LeandroCorrea,

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



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



        Best Regards,
        Angelia