Forum Discussion

marcos_osorio's avatar
marcos_osorio
Helper II
6 years ago

Problem with calculate or Filter

 

Hi, I'm trying to do a simple thing, but somehow don't know how to do it. 

 

I have a table of sales and want to compare with the planned Budget. 

In the table we have a [type] field with the types (Sold, Budget 1, Budget 2, Budget 3, Budget 4).

The problem is that my boss wants to see it on  the format as the picture on the begining.

 

  The first problem is that when I put these on the Matrix the Budget value sum is composed of all Budgets, how can I filter in a way to show the right value based on a Slicer containing only the Budget number?

 

I created the measures for the Qtty for budget and Sold, but and maybe this is part of the solucion to provious problem, How can I group same measures?  In these case Qtty must be (Qtty Sold + Qtty Budget)

 

I appeciate all your help.

 

Thank you very much.

 

 

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    marcos_osorio I think somewhat it can be achieved, if you can share sample pbix file and try to get thru it. Make sure to send the data file with it as well as it will require some sort of transformation in power query and that's only possible if data file is there. If you are directly pasting the data in power bi file then you don't need to send the data file.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If the format is a must, You might have to create a table fit for purpose using union, summarize or other methods.

     

    Example is

     

    union(
summarize(table,table[Type],"Measure","Qty", "Budget",[Budget], "Sold",[Sold],  "diff",[diff]",diff %",[dif%]),
summarize(table,table[Type],"Measure","Amnt", "Budget",[Budget], "Sold",[Sold],  "diff",[diff]",diff %",[dif%])
)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin