Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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
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
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.
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)
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
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
Experts - any help pls ? thanks.
USe countx Something like this should work
COUNTX(FILTER('Table',[Amount]<10),[Invoice])
Please do check and let us know .
Thanks,
Mitsu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |