Forum Discussion

DLHGKreport's avatar
DLHGKreport
New Member
1 year ago

DAX formula help subtotals

Hey all,

 

I wrote a DAX formula, but when i select induvidual Konto's in a slicer i get the right totals, but when i select mutiple the totals and column subtotals are wrong. I can't figure it out: 

Test Calculatie =
VAR A1Pot_loss = (10 / 24) * [Dayvalue]
VAR A2Pot_loss = (6 / 24) * [Dayvalue]
VAR Disponibel_loss = [Dayvalue]
VAR Onderbezetting_loss = [Dayvalue]
VAR Reparatie_loss = [Dayvalue]

-- Dynamic calculation for each Konto in the slicer
VAR RowValue =
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "A1-Vaart", A1Pot_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "A2-Vaart", A2Pot_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Disponibel", Disponibel_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Onderbezetting", Onderbezetting_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Reparatie", Reparatie_loss,
        0
    )

-- Summing dynamically across all Konto values selected in slicer
VAR totalValue =
    SUMX(
        SUMMARIZE(
            'dagen 2024',
            'Ships'[SchiffBez], -- Group by ship
            'dagen 2024'[Konto]      -- Group by Konto
        ),
        SWITCH(
            TRUE(),
            'dagen 2024'[Konto] = "A1-Vaart", A1Pot_loss,
            'dagen 2024'[Konto] = "A2-Vaart", A2Pot_loss,
            'dagen 2024'[Konto] = "Disponibel", Disponibel_loss,
            'dagen 2024'[Konto] = "Onderbezetting", Onderbezetting_loss,
            'dagen 2024'[Konto] = "Reparatie", Reparatie_loss,
            0
        )
    )

-- Return logic ensuring row totals and subtotals respect slicer selections
RETURN
IF(
    ISINSCOPE('dagen 2024'[Konto]),
    RowValue,  -- Calculate per row for selected Konto
    totalValue -- Aggregate across all selected Konto
)

Somebody help pls
Need Help

3 Replies

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

    If the measure [Dayvalue] is a simple aggregation, this measure is fine (because the filtered rows are only traversed once), but if it contains complex logic, then each grouping (SUMMARIZE and the axis in the chart) must be done with caution.

    In the absence of specific data, only simple suggestions can be given.

    • DLHGKreport's avatar
      DLHGKreport
      New Member
      1 year ago

      Im prohibited to share the data but i can show you that the calculation is not working like intended: 


      The [dayvalue] is the following measure:

      Dayvalue =
      VAR _days = [Aantal dagen]
      VAR _dayrate = CALCULATE([Dagprijs], REMOVEFILTERS('dagen 2024'[Konto]))
      RETURN
      _dayrate * _days
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DLHGKreport ,

    When more than one value is selected in the slicer, the filtering context will be more than one value, in which case the second parameter of SELECTEDVALUE will be returned, which defaults to blank (). So the following result will always return 0 when multiple selections are made.

    -- Dynamic calculation for each Konto in the slicer
VAR RowValue =
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "A1-Vaart", A1Pot_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "A2-Vaart", A2Pot_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Disponibel", Disponibel_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Onderbezetting", Onderbezetting_loss,
        SELECTEDVALUE('Vaardagen 2024'[Konto]) = "Reparatie", Reparatie_loss,
        0
    )

    Please consider creating a pbix file with the simulated data and uploading it to a cloud drive and shared link, this will help us find the problem faster.
    How to provide sample data in the Power BI Forum - Microsoft Fabric Community
    Posting the question in the appropriate forum will result in quicker support.
    DAX Commands and Tips - Microsoft Fabric Community

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum