Forum Discussion

pagliaci's avatar
pagliaci
Frequent Visitor
4 years ago
Solved

Compare multiple values against target budget

 Hello,    I have a powerbi where it shows me a report for the total $ spent in the departments, the database is the $ of each product bought, where I have a card that shows me the total amount and...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi pagliaci ,

     

    According to the code, I think you don't need to create a relationship between target table and expense table. We have added the filter in code.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RicoZhou2's avatar
RicoZhou2
Icon for Resolver I rankResolver I
4 years ago

Hi pagliaci ,

 

If your "Expense" table is as below, you can try to Unpivot [Target for Month] columns in "Targer" table in Power Query Editor.

New Table:

Then your calculation will be much easier.

M_Expense = 
CALCULATE(SUM(Expense[Expense]),FILTER(ALL(Expense),Expense[Department] = MAX(Target[Department])&& Expense[Month] in VALUES(Target[Month]))) 
Measure = IF([M_Expense]> SUM(Target[Target]),"Yes","No")

Result is as below.

 

Best Regards.

 

  • pagliaci's avatar
    pagliaci
    Frequent Visitor
    4 years ago

    Thank you. What relations should I make? Because I need to filter by department and by date

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi pagliaci ,

       

      According to the code, I think you don't need to create a relationship between target table and expense table. We have added the filter in code.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.