Roll up Calculations

[Arun_S]

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.

[v-yuezhe-msft · Accepted solution]

@Arun_S,

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]

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]

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]

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.  

[Arun_S]

In my example i have 3 invoices

inv-1 is $6
inv-2 is $4
inv-3 is $11

now if i want to display a Card which just shows me the count of invoices that are less than $10.

As per the above the card should display 2 (which are inv-1 and inv-2) as both of them are less than $10 in value.

But since i currently have only ItemValues and not InvoiceValues, this card displays 6 (each of the itemvalue (qty * rate) is less than $10)

[jday]

To do this all you need to do is follow these steps 

1.  In the modeling tab create a column with the formula 
TotalCost = Table1[QTY]*Table1[Rate] 

2.  Bring the Invoice Column into a table and the TotalCost column you just created your results should look like this 

3.  In the values section under total cost select "Sum" and you will get these results 

4.  Select the visual and click the dropdown for TotalCost and apply a filter where Total Cost  "is less than" 10  and apply the filter and your results will be 

[Arun_S]

Hi - I have no problems displaying in a tabular form (Invoice & InvoiceCost). What i am looking for is to display the count (just the count of invoices) on a Card. so, for the above example, i would like to have a Card which says the number 2

appreciate your help and time

Arun

[Arun_S]

Experts - any help pls ? thanks.

[mitsu]

USe countx Something like this should work 

COUNTX(FILTER('Table',[Amount]<10),[Invoice])

Please do check and let us know .

Thanks,

Mitsu