Forum Discussion

niceday123's avatar
niceday123
Frequent Visitor
6 years ago
Solved

Display data + measure in the same vizualisation

Hi,   I have the following data :   Table1 : Forecast Table2 : Objective I'd like my vizualisation to show that :   Any help will be very much appreciated. Many thanks.
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi niceday123 ,

     

    Measure = 
var a = CALCULATE(SUM(Objective[Sales Objective]),FILTER(Objective,Objective[Name]=SELECTEDVALUE(Forecast[Name])))
var b = CALCULATE(SUM(Forecast[Sales Forecast]),ALLEXCEPT(Forecast,Forecast[Name],Forecast[Place],Forecast[Month]))
return
a-b

    Can't hide but you can change the font color to white under Format -> Field formatting

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

niceday123's avatar
niceday123
Frequent Visitor
6 years ago

Hi vanessafvg , Here is my data as text

 

Name Month Place Sales Forecast   Name Sales Objective
Peter Jan-20 Orlando 2000   Peter 4000
Peter Jan-20 Chicago 2000   John 10000
Peter Feb-20 Miami 1500   Mike 5000
Peter Feb-20 New-York 1700    
Peter Mar-20 Orlando 1000    
John Jan-20 Chicago 2000    
John Jan-20 Miami 2000    
John Jan-20 Orlando 5000    
John Feb-20 Miami 8000    
John Mar-20 New-York 5000    
John Mar-20 Miami 5000    
Mike Jan-20 New-York 3000    
Mike Jan-20 Orlando 3000    
Mike Feb-20 Chicago 2500    
Mike Feb-20 Miami 1000    
Mike Mar-20 Miami 500    
Mike Mar-20 Orlando 500    
Mike Mar-20 New-York 3000    

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi niceday123 ,

 

Measures can only be added into value field of Matrix.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

  • niceday123's avatar
    niceday123
    Frequent Visitor
    6 years ago

    Hi Anonymous , Thanks for your answer.

     

    Could you please share the DAX formula of your measure 3 ?

     

    Also, is there a way to ignore/hide details by Place only for columns Measures 3 and display total of that column as the sum of the subtotal ? i.e. :

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi niceday123 ,

       

      Measure = 
var a = CALCULATE(SUM(Objective[Sales Objective]),FILTER(Objective,Objective[Name]=SELECTEDVALUE(Forecast[Name])))
var b = CALCULATE(SUM(Forecast[Sales Forecast]),ALLEXCEPT(Forecast,Forecast[Name],Forecast[Place],Forecast[Month]))
return
a-b

      Can't hide but you can change the font color to white under Format -> Field formatting

       

      Best Regards,

      Jay

      Community Support Team _ Jay Wang

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

      • niceday123's avatar
        niceday123
        Frequent Visitor
        6 years ago

        Hi Anonymous ,

         

        Thank you, very helpful !

        My last concern is about the total of the column measure which I would like to show the sum of the bold values in the column (i.e. in Jan : 0 + (-1000) + 1000 = 0 )

         

        Thanks