Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Summarizing data

Hello All,

I have created one column in PBI, which gives Fees amount . The dax is below

FEES_CALCULATED =
SWITCH(
TRUE(),
PARTS[FLAG]="PT",
IF(
NOT(ISBLANK([A_FEES_VALUE])) && [A_FEES_VALUE] <> 0,
[A_FEES_VALUE],
IF(
NOT(ISBLANK([A_FEES_MCA])) && [A_FEES_MCA] <> 0,
[A_FEES_MCA],
[A_FEES_LTS]
)
),

BLANK()
)

When I am taking it in card it's sumaating all , for example if i select any ID and if it has 9 rows for that ID and value of fees in this case of ID is 9.44, it's giving as 9.44*10, I am expecting 9.44
If I select average in card, it's totally averaging for all ID's which I don't want
The fees is not seen only based on ID , but also many numbers.

How to neglect multiple rows summating?

Please help, Thanks!

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    I tried to reproduced your data.

    Here's my sample data and the calculated column.

    By default, the card visual displays an aggregate value.

    To avoid summing up the values, you can create a measure that calculates the FEES_CALCULATED for a specific ID. Here’s an example of how you can create such a measure:

     

    FEES_CALCULATED_MEASURE=MAX ( 'PARTS'[FEES_CALCULATED] )

     

     

    This measure will return the maximum FEES_CALCULATED value for the selected ID, ensuring that the card visual displays the correct value.

    If the problem is still not solved, please provide some dummy data and the desired result to show your formula operation, thank you.

     

    Best Regards,

    Stephen Tao

     

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

     

5 Replies

  • ebee's avatar
    ebee
    Helper I
    1 year ago

    Hi,

     

    can you show the dax in [A_FEES_VALUE], [A_FEES_MCA] and/or [A_FEES_LTS]?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Those are direct columns, which are in decimal number data type 

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    Hi Anonymous 

     

    Try with this measure.

    FEES_CALCULATED =
SWITCH(TRUE(),
    PARTS[FLAG] = "PT",
    IF(NOT(ISBLANK([A_FEES_VALUE])) && [A_FEES_VALUE] <> 0,
        [A_FEES_VALUE],
        IF(
            NOT(ISBLANK([A_FEES_MCA])) && [A_FEES_MCA] <> 0,
            [A_FEES_MCA],
            [A_FEES_LTS])),
    BLANK())

FEES_DISPLAY =
SELECTEDVALUE(FEES_CALCULATED, BLANK())

     

    Let me know if it works.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, Thanks for replying.
      It's giving blank values if i didn't select any filter.

      It should be like if I didn't select anything it should summate values from Flag= PT and give total value, but not duplicate 
      Morever I created both FEES_CALCULATED & FEES_DISPLATED  as Columns, in measure it giving some errors

      Please reply back

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    I tried to reproduced your data.

    Here's my sample data and the calculated column.

    By default, the card visual displays an aggregate value.

    To avoid summing up the values, you can create a measure that calculates the FEES_CALCULATED for a specific ID. Here’s an example of how you can create such a measure:

     

    FEES_CALCULATED_MEASURE=MAX ( 'PARTS'[FEES_CALCULATED] )

     

     

    This measure will return the maximum FEES_CALCULATED value for the selected ID, ensuring that the card visual displays the correct value.

    If the problem is still not solved, please provide some dummy data and the desired result to show your formula operation, thank you.

     

    Best Regards,

    Stephen Tao

     

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