Display data + measure in the same vizualisation

[niceday123]

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.

[vanessafvg]

@niceday123  can you paste the data in text format?  

[niceday123]

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]

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]

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 · Accepted solution]

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]

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