Forum Discussion

achen's avatar
achen
Frequent Visitor
6 years ago

Enter Formula Into Matrix

Hi,   I have the following matrix and I would like to fill in the empty fields with a formula that references the other values in the matrix.    The BDG comes from 1 table and the STD and Actual ...
Need Help
achen's avatar
achen
Frequent Visitor
6 years ago

Here is the link to my test file on my google drive as I do not appear to have permissions yet to include attachments.

 

https://drive.google.com/open?id=1eiwwKHMArl17-AGtv-LQYRLCKbvLbGzU

 

In the attached example, for each month, I need to take  BDG Others / BDG Other Expenses * STD Other Expenses and that value from the formula should appear in STD Others field for each month

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi achen ,

You can try to use the following measure formula to manually replace blank cell values:

Measure = 
IF (
    SELECTEDVALUE ( STD[Subcost] ) IN VALUES ( Table1[SubCost] ),
    CALCULATE (
        SUM ( STD[Value] ),
        FILTER (
            ALLSELECTED ( STD ),
            STD[Date] = MAX ( BDG[Date] )
                && [Subcost] IN VALUES ( Table1[SubCost] )
        )
    ),
    IF (
        INTERSECT ( ALLSELECTED ( STD[Date] ), VALUES ( BDG[Date] ) ),
        VAR others =
            CALCULATE (
                SUM ( BDG[Value] ),
                ALLSELECTED ( BDG ),
                BDG[SubCost] = "Others",
                VALUES ( BDG[Date] )
            )
        VAR otherex =
            CALCULATE (
                SUM ( BDG[Value] ),
                ALLSELECTED ( BDG ),
                BDG[SubCost] = "Other Expenses",
                VALUES ( BDG[Date] )
            )
        VAR stdotherex =
            CALCULATE (
                SUM ( STD[Value] ),
                FILTER (
                    ALLSELECTED ( STD ),
                    STD[SubCost] = "Other Expenses"
                        && STD[Date] = MAX ( BDG[Date] )
                )
            )
        VAR Rentals =
            CALCULATE (
                SUM ( BDG[Value] ),
                ALLSELECTED ( BDG ),
                BDG[SubCost] = "Rentals",
                VALUES ( BDG[Date] )
            )
        RETURN
            SWITCH (
                SELECTEDVALUE ( Table1[SubCost] ),
                "Others", others / otherex * stdotherex,
                "Rentals", Rentals / others * ( others / otherex * stdotherex )
            )
    )
)

Notice: power bi does not contain row/column index and it does not allow you to loop text values, my formula is hardcode based on its row contents.

Regards,

Xiaoxin Sheng