Forum Discussion

Ståle's avatar
Ståle
New Member
5 years ago

Conditional sum, measures from different tables

Hi

 

I have two tables with no shared key, but related through properties. The tables themselves would be many-to-many through the properties. As far as I can tell, there is no way for me to join the two tables directly without loosing slicing options.

 

My problem is that I need to calculate the difference between two sums, one from each of the tables, but with the condition that only the positive differences should count (negative basically set to 0). So the idea is to show a column with sum(quantity in)-sum(quanity out). This is easily done within the table rows, but the total (which is an important measure) wont show the correct value.

 

Due to the confidentiality of the data, I cannot share too much, however, the data model should be ok to post.

 

I create a visual containing:

  1. Kunder[Kunde]
  2. Mottaksstasjon[Mottaksstasjon]
  3. Mottaksstasjon[Sted]
  4. Kalender[Uke]

With measures:

  1. Sum(Salg[Kvantum])
  2. Sum(Seddel[Kvantum])
  3. Sum(If(Sum(Salg[Kvantum])-Sum(Seddel[Kvantum])<0,0,Sum(Salg[Kvantum])-Sum(Seddel[Kvantum])))

 

Measure 3 is the problematic one, I cannot get the total correct, please help.

 

Kind regards,

Ståle

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Ståle , fro correct total you have force a correct context like

    sumx(kanendar, Sum(If(Sum(Salg[Kvantum])-Sum(Seddel[Kvantum])<0,0,Sum(Salg[Kvantum])-Sum(Seddel[Kvantum]))))

     

    or , based on the group/axis  in the visual, example 

    sumx(values(kanendar[date]), Sum(If(Sum(Salg[Kvantum])-Sum(Seddel[Kvantum])<0,0,Sum(Salg[Kvantum])-Sum(Seddel[Kvantum]))))

    • Ståle's avatar
      Ståle
      New Member
      5 years ago

      Thx for your quick reply amitchandak !

       

      I dont think this solution would work. I dont have a "table" to use sumx on. If I try, also the individual rows calculates incorrectly, I believe due to the many-to-many issue. It also returns wrong results when excluding the conditional and trying a simpler form for testing:

      SUMX(VALUES(Kalender[date]),SUM(Salg[Kvantum])-SUM(Seddel[Kvantum]))

       

      In my head, the logic must be something like:

      1. Calculate the aggregates on row to row basis from the visual, and "not" from any table.
      2. Calculate the difference and check if it is positive
      3. Sum up all positive differences as if the visual itself was a table

      I tried to use summarize to turn visual into a temp table, but couldnt make it work either.

       

      Best regards,

      Ståle