Forum Discussion

Regular Visitor

8 years ago

Solved

Roll up Calculations

Hi - I am new to Power BI. Assuming the above is my dataset, I can now create a new column for ItemCost (Qty * Rate). How can I create a column or measure for InvoiceCost. I am looking at way to create sum(qty*rate) for each Invoice.

I use this in Tableau: {FIXED [Invoice_Num] : SUM([ItemCost])} ... looking for an equivalent in Power BI.

thanks for your time in advance.

Anonymous

Create a measure using DAX below.

count = 
VAR tab= SUMMARIZE(Table,Table[Invoice_Num],"cost",SUMX(Table,Table[Qty]*Table[Rate]))

return
CALCULATE(COUNTROWS(FILTER(tab,[cost]<10)))

Regards,
Lydia

9 Replies

Anonymous

Not applicable

Create a measure using DAX below.

count = 
VAR tab= SUMMARIZE(Table,Table[Invoice_Num],"cost",SUMX(Table,Table[Qty]*Table[Rate]))

return
CALCULATE(COUNTROWS(FILTER(tab,[cost]<10)))

Regards,
Lydia

jday
Helper I
8 years ago
Hello,

You can create the new column that would give you the Item Cost
Total Cost= Table[Qty]*Table[Rate]

Once you do this you can create a measure that sums the Total Cost
Sum Total Cost = SUM(Table[Total Cost])
Then create your visualization and bring in the Invoice_Num and the Sum Total Cost Measure you created
- Arun_S
  Regular Visitor
  8 years ago
  Hi..thanks for looking into this.
  
  I should have mentioned the senario i am working on...I was able to display invoice costs by pulling in Inv_Num & Cost....I do still want to have a measure / column which will give me cost at Invoice level.
  
  here is a business scenario: I want to display the total number (count) of Invoices where Invoice value is less than $500. Right now, i am not able to do this, b'cos i only have ItemValue...
  
  thanks
  Arun
  - jday
    Helper I
    8 years ago
    You can add a filter under the visual level filters and have the Total be less than 500. If this does not produce what you would like could you provide an example of the desired results in relation to the example dataset you provided.

Related content