Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Using report view values to calculate another value

lo,

 

I'm quite new to Power BI so apologies if this is a silly question but I cannot find the solution to something I thought would be easy in the same way I would do it in Excel.

 

In Report view, I have a column which uses DAX to Sum it's value (from thousand of rows). I have another column which pulls through a static value from another table. Both are displayed in Report View based on SiteID.

 

I would like to do a further calculation whereby I want to display the value of Column A minus (-) column B = column C.

I can't get this to work at table level as I end up with duplicated rows showing in Report View, i think because final values are currently being automatically summed in the Report View.

 

Can I do this at Report level please?

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Just to add, if I use TableC[SiteID] on the left side of the = operator, it then doesn't show TableA[SiteID] on the right side of the = operator.

  • devesh_gupta's avatar
    devesh_gupta
    Impactful Individual
    2 years ago

    Anonymous I'm unable to get your last point but let's try to create a simple measure from these two columns just like:

    measure = sum('table'[column A]) - sum('table'[column B])

    Or else if it doesn't work, can you provide me the sample data for the same.

     

    If you find this insightful, please provide a Kudo and accept this as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Many thanks for your help.

       

      Having looked at this further, I think the issue is to do with my table relationships.

       

      I have the following structure:

      Table A      <->1:1      Table B      ->1:many      Table C

      10 rows                       10 rows                          1,000 rows

       

      I want to add a column in Table A whereby it sums the multiple values of a column in Table C. 

       

      However, the formula I use doesn't give me the option to filter based on column "Site ID" .

      I want to use: CALCULATE(SUM(TableC[column1]),TableA[SiteID]=TableC[SiteID]))

      However, I it doesn't give me the option to locate TableC[SiteID] when used on the right side of the = operator. It does, however, show it if I use it on the left hand side of the = operator.

       

      I have tried changing my table relationships so that it becomes the following but it makes no difference.

      Table A      <-1:many      Table B      ->1:many      Table C

      10 rows                          10 rows                          1,000 rows

       

      Is there a command I can use to pull in the TableC[SiteID] value in my CALCULATE equation? I've tried RELATED but this hasn't worked.

       

      Any help much appreciated! thank you.