Summarizing, adjusting, and filtering data

[Alex1928361]

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 altering the sum based off of what the "Description" is. I also would like the ability to ignore data with 'MA' listed as the "State". The actual data can't be shared, but is fairly straightforward- it looks like this (there are roughly 1 thousand "PersonIDs", 10 different "Descriptions", and roughly 3 thousand rows of data in the real set):

PersonID	Description	Money	State
001	Group1	10	MA
002	Group1	5	NH
003	Group2	6	AK
003	Group5	1	NH
004	Group8	15	NH
004	Group8	15	NH
005	Group8	20	MA

I need "Money" summarized, with 2 "Money" being subtracted from the total summarized amount for each PersonID/Group combo, except for Group8 which needs 3 subtracted instead. The results cannot be negative, it is essentially a tax on each group. I want to then graph how much money each group has left, and how much has been taxed. The results for this example would look like this.

PersonID 1:

Group1: 8 (because 10 - 2 tax)

PersonID 2:

Group1: 3 (because 5 - 2 tax)

PersonID 3:

Group2: 4 ( because 6 - 2 tax)

Group5: 0 (becuase 1 - 2 tax but it cannot tax money they dont have)

PersonID 4:

Group8: 27 (because 30 - 3 tax)

PersonID 5:

Group8: 17 (because 20 - 3 tax)

My attempts so far have undoubtedly been ham-fisted, but here is my current approach. The following is a custom column meant to group by PersonID, a specific Description, then filter out certian states, and then I made a column like this for each of the 8 descriptions. It is in Power BI desktop, which I beleive is DAX.

TotalGroup1 = 
CALCULATE (
    SUM ( 'Alex1'[Money] ),
    FILTER (
        ALL ( 'Alex1' ),
        [PersonID] = EARLIER ( 'Alex1'[PersonID] ) &&
        (
            'Alex1'[State] = "MA" ||
            'Alex1'[State] = "NH" ||
            'Alex1'[State] = "TX" ||
            'Alex1'[State] = "AK"
        ) &&
        'Alex1'[Description] = "Group1"
    )
)

I then made this custom column to adjust it based off of description

TotalAdjusted = 
SWITCH(
    'Alex1'[Description],
    "Group1", IF('Alex1'[TotalGroup1] - 250000 < 0, 0, 'Alex1'[TotalGroup1] - 250000),
    "Group2", IF('Alex1'[TotalGroup2] - 250000 < 0, 0, 'Alex1'[TotalGroup2] - 250000),
    "Group3", IF('Alex1'[TotalGroup3] - 250000 < 0, 0, 'Alex1'[TotalGroup3] - 250000),
    "Group4", IF('Alex1'[TotalGroup4] - 250000 < 0, 0, 'Alex1'[TotalGroup4] - 250000),
    "Group5", IF('Alex1'[TotalGroup5] - 250000 < 0, 0, 'Alex1'[TotalGroup5] - 250000),
    "Group6", IF('Alex1'[TotalGroup6] - 250000 < 0, 0, 'Alex1'[TotalGroup6] - 250000),
    "Group7", IF('Alex1'[TotalGroup7] - 250000 < 0, 0, 'Alex1'[TotalGroup7] - 250000),
    "Group8", IF('Alex1'[TotalGroup8] - 250000 < 0, 0, 'Alex1'[TotalGroup8] - 250000), 
    "Group9", IF('Alex1'[TotalGroup9] - 250000 < 0, 0, 'Alex1'[TotalGroup9] - 250000),
    "Group10", IF('Alex1'[TotalGroup10] - 250000 < 0, 0, 'Alex1'[TotalGroup10] - 250000),
    'Alex1'[Money]
)

  The main problem with this is that it now gives every column the total for that PersonID/Description combo, and trying to make a visual that shows the overarching total for how much money everyone has and how much has been taxed is a nightmare. If anyone needs more information please feel free to leave a comment, this is my first post on here and I'm happy to update / amend this in any way. 

[Anonymous]

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]

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 · Accepted solution]

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