Forum Discussion

Robbinsbk's avatar
Robbinsbk
Regular Visitor
6 years ago
Solved

Add a calculated column to a Matrix visualization

I am using a Power Bi matrix visualization to display a series of data. I have two datasources.  The first is my data I want to display and the second is my target values.  There is a Mant to One rel...
Need Help
  • Robbinsbk's avatar
    Robbinsbk
    6 years ago

    Got it to work.  (Used a new name) What I did was use a measure.  I only wanted one value for the target. The relationship is Many to One.  Since there is only one value in the target type for each category I used MIN (again because there is only one value I could have just as easily used MAX) instead of sum for the target.

     

    To Be Released = CALCULATE( MIN(SuperMarketData[Target]),ALLSELECTED(TargetData)) - CALCULATE( SUM(SuperMarketData[Before SuperMarket]) + SUM(SuperMarketData[In SuperMarket]),ALLSELECTED(TargetData))
     

     

     
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Is your "GAP" column in the matrix a calculated column or a measure? Make sure it is a calculated column and then select SUM as the aggregation.

 

If this helps please kudo.

If this solves your problem please accept it as a solution.

  • Robbinsbk's avatar
    Robbinsbk
    Regular Visitor
    6 years ago

    Yes, it is a calculated column.  I tried a couple different calculations but none is working.

     

    Gap = SUM(SuperMarketData[Target]) - SUM(SuperMarketData[SuperMarket WIP])
    Gap = CALCULATE(SUM(SuperMarketData[SuperMarket WIP]) - SUM(SuperMarketData[Target]))

    Gap = CALCULATE(SUM(SuperMarketData[Target]) - SUM(SuperMarketData[SuperMarket WIP]),FILTER(SUMMARIZE(SuperMarketData,SuperMarketData[Die Type]),SuperMarketData[Die Type]=RELATED(TargetData[DIE_TYPE])))
     
    • Robbinsbk's avatar
      Robbinsbk
      Regular Visitor
      6 years ago

      Got it to work.  (Used a new name) What I did was use a measure.  I only wanted one value for the target. The relationship is Many to One.  Since there is only one value in the target type for each category I used MIN (again because there is only one value I could have just as easily used MAX) instead of sum for the target.

       

      To Be Released = CALCULATE( MIN(SuperMarketData[Target]),ALLSELECTED(TargetData)) - CALCULATE( SUM(SuperMarketData[Before SuperMarket]) + SUM(SuperMarketData[In SuperMarket]),ALLSELECTED(TargetData))