Forum Discussion

Rbakker888's avatar
Rbakker888
Icon for Helper II rankHelper II
1 year ago
Solved

Sumarization issue with DAX formula

I am trying to make some visuals with a formula for revenue where I multiply the revenue by 10 times if the product is a contract, the issue I am having is that it seems to summarize all the revenue ...
Need Help
  • Deku's avatar
    Deku
    1 year ago

    Try the second solution and this

     

    SUMX(

    Values(Dimension[ArtikelCategorie]),

    Calculate( sum(fact_Orders[Order Marge Bedrag €] )) *

     IF (

     Dimension[ArtikelCategorie] = "Contract", 10, 1)

    )

Deku's avatar
Deku
Icon for Super User rankSuper User
1 year ago

This is like due to autoexist, please see this SQLBI video on the topic, and this blog post fro jeffery wang

 

In essence filters on the same table are grouped into tuples. When you remove a filter you remove from the tuple rather than the column, giving unexpected results

 

You have two options

  • Adjust the summarizecolumn() behaviour, as per the article, to Independent 

 

  • create a dimension for categories, and use this for your logic. Then the filter are coming from two different tables and autoexist doesn't kick in
  • Rbakker888's avatar
    Rbakker888
    Icon for Helper II rankHelper II
    1 year ago

    Deku I tried this but changing the Value Filter behaviour doesnt seem to change anything and the second solution also does not seem to work or I am doing it wrong. For the second solution, I just tried to copy the table and then use the column there for the categories.

     

    • Deku's avatar
      Deku
      Icon for Super User rankSuper User
      1 year ago

      Try the second solution and this

       

      SUMX(

      Values(Dimension[ArtikelCategorie]),

      Calculate( sum(fact_Orders[Order Marge Bedrag €] )) *

       IF (

       Dimension[ArtikelCategorie] = "Contract", 10, 1)

      )

      • Rbakker888's avatar
        Rbakker888
        Icon for Helper II rankHelper II
        1 year ago

        Due to some of how my semantic model is build I had to very slightly edit it but this got me the correct base, thank you.
        My final formula:

        SUMX(
        VALUES(Dimension[ArtikelCategorie]),
        CALCULATE( [Order Marge Bedrag €] ) *
        IF( SELECTEDVALUE(Dimension[ArtikelCategorie]) = "Contract", 10, 1 )
        )