Forum Discussion

Arun_S's avatar
Arun_S
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 wa...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
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's avatar
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's avatar
    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.  

     

     

    • Arun_S's avatar
      Arun_S
      Regular Visitor
      8 years ago

      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's avatar
        jday
        Helper I
        8 years ago

        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