Search code examples
ssasbusiness-intelligencedax

How to summarize on distinct values that group other values

I have this table:

Family_ID Person_ID Weight Spent_amount category
A           1         10       500        flight
A           2         10       500        flight
A           1         10       200        Hotel
A           2         10       200        Hotel
B           3         20       250        flight
B           3         20       300        Hotel

as we see here every family has a member and the costs per category calculated for the family not the person. And as we see not every family is equal to other but it has weight so every spent should be multiplied by family weight. Now my goal is to write a measure for spent for every family

I wrote an equation but I see its very complex and I guess it give a wrong value. The Dax Equation I wrote is

cash_wieght:=SUMX (
    SUMMARIZE (
s2g516_full,
s2g516_full[DIM_HOUSEHOLD_REF_ID],
"cash", CALCULATE ( MAXX ( SUMMARIZE (
s2g516_full,
  s2g516_full[Persons],
  s2g516_full[DIM_HOUSEHOLD_REF_ID] ,"cash1", CALCULATE ( Sum(s2g516_full[SPENT_PER_REASON])*MAX ( s2g516_full[Weight] ) )
/distinctcount(s2g516_full[person])  ),[Cash1] ) )
 ),
    [cash]
)

When I took a sample by filtering with family_ID, it gave me the correct numbers for samples tests but maybe not of them. So how can I tell if I got the right results or not?

The result I want is the weight * spent_amount to be summed and filtered with family_ID as if person_ID is not in the table like: "if I filtered on Family_ID"

A 700*10
B 550 *20
Solution

  • First, DAX Formatter is your friend.

    cash_wieght :=
SUMX (
    SUMMARIZE (
        s2g516_full,
        s2g516_full[DIM_HOUSEHOLD_REF_ID],
        "cash", CALCULATE (
            MAXX (
                SUMMARIZE (
                    s2g516_full,
                    s2g516_full[Persons],
                    s2g516_full[DIM_HOUSEHOLD_REF_ID],
                    "cash1", CALCULATE (
                        SUM ( s2g516_full[SPENT_PER_REASON] ) * MAX ( s2g516_full[Weight] )
                    )
                        / DISTINCTCOUNT ( s2g516_full[person] )
                ),
                [Cash1]
            )
        )
    ),
    [cash]
)

    After that, I think you've got the right intuition with SUMMARIZE(), but it can be much simpler than what you're doing. SUMMARIZE() groups by the fields in a table that you pass to it. Since members with the same [Family_ID] have the same values for [Spent_Amount] in a given [Category], all we need to do is group by everything from the table except [Person_ID].

    cash_wieght:=
SUMX (
    SUMMARIZE(
        FactSpend
        ,FactSpend[Family_ID]
        ,FactSpend[Weight]
        ,FactSpend[Category]
        ,FactSpend[Spent_Amount]
    )
    ,FactSpend[Weight] * FactSpend[Spent_Amount]
)

    Here we use SUMMARIZE() to group by [Family_ID], [Weight], [Category], and [Spent_Amount]. Since addition and multiplication are commutative, it doesn't matter if we add first or multiply first.

    So we use SUMX() to iterate over each row returned by our SUMMARIZE() and accumulate into a sum the values of [Weight] * [Spent_Amount] for each row in that grouped table.

    Here's an image of my sample data and pivot table based on this measure performing appropriately. It also works when adding any of the attributes from the table to filter context. If one [Person_ID] is in context, the measure will return the value for the family, and at the family level will return the value for the family. We can alter the measure to only give a single [Person_ID]'s portion of the [Family_ID]'s whole if necessary.

    enter image description here