Forum Discussion
Roll up Calculations
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
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 this3. 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.