Forum Discussion

Micky1968's avatar
Micky1968
Frequent Visitor
7 months ago
Solved

Wrong total in list

Dear all,
I know there are already a lot of topics with this issue but I can't get it to work:
I have two tables:
Table1 only 1 field with the name serials
Table2 with all the data including the field Device_serial_id which contains the serials from the Table1

I have linked both tables (Table 1 = One to many (Table2)
So far so good.

So I want to calculate the number of Claims for each Serials, which I have done with:

Claims = SUM('Table2'[quantity])
As you can see I get a total of 4414 but it should be 2561.
Can you lead me to the right formula please ?
I have tried also with "Count" but I get the same total.
Thank you so much for your help.
Kind regards
Michael
  • Micky1968's avatar
    Micky1968
    7 months ago

    Dear all,
    I think I found the solution with some help from Youtube and reading:
    The DAX should be 

    Claims = COUNTX('Tabelle2',('Tabelle2'[Anzahl]))
    Claims correct = sumx( SUMMARIZE('Tabelle1','Tabelle1'[Serials]), CALCULATE('Tabelle2'[Claims]))
    I added the Claims correct field to the List and the total is now correct.

     

    Reading and listening helps 🙂

2 Replies

  • Micky1968's avatar
    Micky1968
    Frequent Visitor
    7 months ago

    Dear all,
    I think I found the solution with some help from Youtube and reading:
    The DAX should be 

    Claims = COUNTX('Tabelle2',('Tabelle2'[Anzahl]))
    Claims correct = sumx( SUMMARIZE('Tabelle1','Tabelle1'[Serials]), CALCULATE('Tabelle2'[Claims]))
    I added the Claims correct field to the List and the total is now correct.

     

    Reading and listening helps 🙂
    • FBergamaschi's avatar
      FBergamaschi
      Super User
      7 months ago

      Hello Micky1968,

      I would write it in a simpler way to make it more readable and using more common DAX functions

       

      Claims = COUNTROWS ( Tabelle 2 )   -- I am supposing that every single row of Tabelle 2 is a claim

      Claims by Serial = SUMX ( VALUES('Tabelle1'[Serials]), [Claims] )

      If the above works, it is much easier to read

       

      If you want details on VALUES vs SUMMARIZE and the reason to avoid the explicit CALCULATE call, come back in another post to keep things tidy

       

      Best

       

      If this helped, please consider giving kudos and mark as a solution

      @me in replies or I'll lose your thread

      Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

      Consider voting this Power BI idea

      Francesco Bergamaschi

      MBA, M.Eng, M.Econ, Professor of BI