Forum Discussion

dgilm33's avatar
dgilm33
Icon for Helper I rankHelper I
3 years ago
Solved

How to create a Calculate All from separate tables?

Hello,

I'm trying to create a % to put in a table where the formula is a Calculate All. I'm calcuating the sum in one table, but the All columns are coming from a second table. I've tried doing this with both columns and measures but both ways the %'s come back as 100% on every row. I apolgoize as I can't share the data. An example of what I'm trying to accomplish would be like below:

Table 1:                        Table 2:
Calculated Spend        Name
                                    Address
                                    Phone Number
Var Spend = Calculate(Sum(Calculated Spend))
Var TotalSpend = Calculate(Sum(Calculated Spend), ALL(Name, Address, Phone Number))
Return DIVIDE(Spend, TotalSpend)

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi dgilm33 ,

    Please have a try.

    Modify the measure.

    Measure =
VAR Spend =
    CALCULATE ( SUM ( Table1[Calculated Spend] ) )
VAR TotalSpend =
    CALCULATE (
        SUM ( Table1[Calculated Spend] ),
        FILTER (
            ALL ( Table2 ),
            Table2[name] = SELECTEDVALUE ( Table2[name] )
                && Table2[adress] = SELECTEDVALUE ( Table2[adress] )
                && Table2[phone number] = SELECTEDVALUE ( Table2[phone number] )
        )
    )
RETURN
    DIVIDE ( Spend, TotalSpend )

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi dgilm33 ,

    Please have a try.

    Modify the measure.

    Measure =
VAR Spend =
    CALCULATE ( SUM ( Table1[Calculated Spend] ) )
VAR TotalSpend =
    CALCULATE (
        SUM ( Table1[Calculated Spend] ),
        FILTER (
            ALL ( Table2 ),
            Table2[name] = SELECTEDVALUE ( Table2[name] )
                && Table2[adress] = SELECTEDVALUE ( Table2[adress] )
                && Table2[phone number] = SELECTEDVALUE ( Table2[phone number] )
        )
    )
RETURN
    DIVIDE ( Spend, TotalSpend )

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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