Forum Discussion

Alex1928361's avatar
Alex1928361
New Member
2 years ago
Solved

Summarizing, adjusting, and filtering data

Hi everyone, I am somewhat new to Power BI, and this is driving me crazy. Any help would be greatly appreciated. I need to summarize "Money", grouping by both "PersonID" and "Description" while also ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Alex1928361 ,

    Thanks for the reply, create a MEASUREMENT for calculating the group ordinal number on top of the previous one, and then do the calculation on the final result

    Group number = RIGHT(SELECTEDVALUE('Table'[Description]),1)
    TotalAdjusted = 
IF(
    [Total by group] - [Group number] < 0,
    0,
    [Total by group] - [Group number]
)

    Final output

    Best regards,
    Albert He

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

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Alex1928361 ，
According to your description, you mentioned not to calculate the value of satate as “MA”, so I made some additions to the test data. Here is my test data and steps:


Create measures

Total by group = 
CALCULATE(
    SUM('Table'[Money]),
    FILTER(
        'Table',
        SELECTEDVALUE('Table'[State]) <> "MA"
    ),
    ALLEXCEPT(
            'Table',
            'Table'[PersonID],
            'Table'[Description]
    )
)
TotalAdjusted = 
IF(
    SELECTEDVALUE('Table'[Description]) = "Group8" ,
    IF(
        [Total by group] - 3 > 0,
        [Total by group] - 3,
        0
    ),
    IF(
        [Total by group] - 2 > 0,
        [Total by group] - 2,
        0
    )
)

If you don't want to see the “MA” data in the visualization, you can set it in the filters

 

Best regards,
Albert He

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

 

 

Alex1928361's avatar
Alex1928361
New Member
2 years ago

Hello,

 

I very much appreciate the effort, but it is just returning the exact amount of money in each data entry (aka each row). I'm worried I was not specific enough- let me clarify. I need to create a quote on quote 'bin' showing "Money" totals for each combination of "PersonID" and "Description". For example it would add up all entries listed as Person ID 1 and Description Group1 (of which there could be dozens), then add up the totals for all entries listed as Person ID 1 and Description Group 2, then Person ID 2 and Description Group 1, and so on. Then, for each of those bins, it would adjust the total value based off of the description group, for example Person ID 5 and Description Group 8 would subtract 8 Money because it is affiliated with Description group 8.

 

Excluding the state MA can be ignored for the moment, I can add that in later. I believe your code to adjust the values would also work, and again thank you. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Alex1928361 ,

    Thanks for the reply, create a MEASUREMENT for calculating the group ordinal number on top of the previous one, and then do the calculation on the final result

    Group number = RIGHT(SELECTEDVALUE('Table'[Description]),1)
    TotalAdjusted = 
IF(
    [Total by group] - [Group number] < 0,
    0,
    [Total by group] - [Group number]
)

    Final output

    Best regards,
    Albert He

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