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 comes from another.

 

What I like to do is fill in Row F of the STD column with the formula BDG Row F / BDG Row E * STD Row E. So the actual calculation would be 34/51*37. 

 

Is this something I can accomplish? I am not entirely sure where to start with this.

 

Need Help

6 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    It's not really clear what your base data looks like but perhaps

    you could write a measure that tests for blank values and

    if it finds a blank, then tries to compute a value from the other rows.

    Being able to detect the blank value would be the first step.

    Another approach would be to investigate why there are blank rows in the data you are

    loading, and take steps to fill in the values there...before you load the data into power bi.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI achen ,

    For your requirement, you need to add if statement to check current row contents and replace blank value with specific dax formulas.

    BTW, does these blank parts really existed in your table? If not, I think you need to create a new table with all column fields to replace original column field, then you can write formulas to use current category and column label to calculate.

    Regards,

    Xiaoxin Sheng

  • achen's avatar
    achen
    Frequent Visitor
    6 years ago

    No the table that the STD calculates from doesn not have values at all for F. F comes from the BDG table and from that I need to derive hte desired value.

     

    I managed to replace the empty cells with a generic value like 0 or no value. But I am having issues getting my calculated value into those fields

     

    I tried creating 4 seperate measures. I added them to seperate cards and I get my desired values for each. I realize I dont need to seperate them but it helps keep track as I am new to PowerBi.

     

    M1= CALCULATE(SUM(Bdg[BDG]),'Bdg'[Cost]="F")
    M2= CALCULATE(SUM(Bdg[BDG]),'Bdg'[Cost]="E")
    M3= CALCULATE(SUM(DB[STD]),DB[Group1]="E")
    M4 = M1/M2*M3
     
    But when I add M4 into my matrix, it comes up empty. I am guessing it because it doesnt know how to relate the calculated value to the row? How would I go about linking that are assigning my final value to F?
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI achen ,

      Can you please share some sample data for test? It will be help for test to coding formula.

      Notice: do mask on sensitive data or make some fake data with same data structure for test.

      Regards,

      Xiaoxin Sheng

  • 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