Forum Discussion

Gubazi's avatar
Gubazi
New Member
3 years ago
Solved

Subtotals Error

Hello everyone,
I am new to Power BI. I would like to briefly summarize the table below and move on to my question. In the Stock column, there are stocks purchased at different times. "net lot" is the lot remaining after the sales, "avr_buy_price" is the weighted average price of the purchases, "sum_cost" is 'net lot' multiplied by 'avr_buy_price', "sum_value" is the current price multiplied by "net lot" and "sum profit" is the profit with "sum value"-"sum cost".
When I look at the individual rows, I see that the values are correct, but the subtotal is different from the data in the row, i.e. incorrect.
I am attaching the measures below and I would like your help.

 

 

Net Lot = CALCULATE(SUMX(Tablo_hisse_alis_satis, Tablo_hisse_alis_satis[Lot Sayısı]), Tablo_hisse_alis_satis[Alış/Satış] = "Alış") - CALCULATE(SUMX(Tablo_hisse_alis_satis, Tablo_hisse_alis_satis[Lot Sayısı]), Tablo_hisse_alis_satis[Alış/Satış] = "Satış")
 
---
 
Avr_Buy_Price =
    VAR AlisToplam =
        SUMX(
            FILTER(
                Tablo_hisse_alis_satis,
                Tablo_hisse_alis_satis[Alış/Satış] = "Alış"
            ),
            Tablo_hisse_alis_satis[Lot Sayısı] * Tablo_hisse_alis_satis[Fiyat]
        )
    VAR LotToplam =
        SUMX(
            FILTER(
                Tablo_hisse_alis_satis,
                Tablo_hisse_alis_satis[Alış/Satış] = "Alış"
            ),
            Tablo_hisse_alis_satis[Lot Sayısı]
        )
    RETURN
        DIVIDE(AlisToplam, LotToplam)
 
---
 
Sum_Cost = 'Olcu_Tablo'[Net Lot]*'Olcu_Tablo'[Avr_Buy_Price]
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Gubazi ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Measure = SUMX(SUMMARIZE('Tablo_hisse_alis_satis',Tablo_hisse_alis_satis[Stock]),[Avr_Buy_Price]*[Net Lot])

     Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    Hello Gubazi ,

     

    For sum_cost column also use SUMX to match the totals. Your measure should look like this:

     

    Sum_Cost = SUMX('Olcu_Tablo','Olcu_Tablo'[Net Lot])*SUMX('Olcu_Tablo','Olcu_Tablo'[Avr_Buy_Price])

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

    • Gubazi's avatar
      Gubazi
      New Member
      3 years ago

      Hello Kishore_KVN 

       

      Thanks for answer.

      Unfortunately, the measure works but "can not be loaded for the visulation."

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Community Support
        3 years ago

        Hi Gubazi ,

         

        Based on your description, I have created a simple sample:

        Please try:

        Measure = SUMX(SUMMARIZE('Tablo_hisse_alis_satis',Tablo_hisse_alis_satis[Stock]),[Avr_Buy_Price]*[Net Lot])

         Final output:

        Best Regards,

        Jianbo Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.