Chart from a data model (value from cell)

Anything related to PowerPivot and DAX Formuale
luca
Posts: 17
Joined: Sun Mar 03, 2019 6:08 am

Chart from a data model (value from cell)

Postby luca » Wed Apr 10, 2019 7:03 pm

Hi,

in my excel model i would like to use only charts which retrieve data directly from the data model (without passing through a pivot table).
The problem is that i am not able to adjust values as in a normal chart, in specific i am speaking about "value from cell" option, which usually allows inserting in a clustered chart the difference between 2 columns (e.g. CY vs PY) always in the right position using a hidden line plus "value from cell".

Is there any workaround to have the same results which a chart directly linked to the Data Model.
A picture of the desired output attached
thanks
Luca
Attachments
Untitled.png
Untitled.png (3.25 KiB) Viewed 110 times

PhilC
Posts: 219
Joined: Tue Sep 09, 2014 8:13 am

Re: Chart from a data model (value from cell)

Postby PhilC » Thu Apr 11, 2019 11:14 am

Hi Luca,

Could CUBEVALUE be what you are after?

https://powerpivotpro.com/2010/06/using ... owerpivot/
https://www.excelcampus.com/cubevalue-formulas/
https://www.turtle.works/knowledge/dyna ... functions/
https://powerpivotpro.com/category/skil ... -formulas/

CUBE formulas can be used to extract data points from the data model, you provide the co-ordinates to use by including the various dimensions. Therefore you can build up tables / single points of data and have dynamic CUBEVALUE formulas to retrieve the required value from the data model.

Cheers
Phil

luca
Posts: 17
Joined: Sun Mar 03, 2019 6:08 am

Re: Chart from a data model (value from cell)

Postby luca » Thu Apr 11, 2019 5:14 pm

PhilC {L_WROTE}{L_COLON}Hi Luca,

Could CUBEVALUE be what you are after?

https://powerpivotpro.com/2010/06/using ... owerpivot/
https://www.excelcampus.com/cubevalue-formulas/
https://www.turtle.works/knowledge/dyna ... functions/
https://powerpivotpro.com/category/skil ... -formulas/

CUBE formulas can be used to extract data points from the data model, you provide the co-ordinates to use by including the various dimensions. Therefore you can build up tables / single points of data and have dynamic CUBEVALUE formulas to retrieve the required value from the data model.

Cheers
Phil


Hi Phil,
thank you as always,
but CubeValue cant be inserted in the "value from cell" string in a chart, so i cant retrieve the delta% using that directly in the "value from cell", actually i am using that in a table and then i am selecting that range in the "value from cell" but this is not the solution as i always need to pass through a pivot table (or a range)

PhilC
Posts: 219
Joined: Tue Sep 09, 2014 8:13 am

Re: Chart from a data model (value from cell)

Postby PhilC » Sun Apr 14, 2019 9:26 pm

Hi luca,

Am a bit confused. In a chart, Value from Cell is exactly that, using values from a cell range. By using he CUBEVALUE formulas, you are putting values from the underlying data model into cells, that can be referenced in the chart, without having to have a pivot table initially.

Obviously this is not what you are after, so perhaps will need a file posted to be able to see exactly what you re trying to achieve. It does take time, but if the data is confidential, mock up something with fake data and provide that.

Cheers
Phil

luca
Posts: 17
Joined: Sun Mar 03, 2019 6:08 am

Re: Chart from a data model (value from cell)

Postby luca » Tue Apr 16, 2019 7:03 pm

PhilC {L_WROTE}{L_COLON}Hi luca,

Am a bit confused. In a chart, Value from Cell is exactly that, using values from a cell range. By using he CUBEVALUE formulas, you are putting values from the underlying data model into cells, that can be referenced in the chart, without having to have a pivot table initially.

Obviously this is not what you are after, so perhaps will need a file posted to be able to see exactly what you re trying to achieve. It does take time, but if the data is confidential, mock up something with fake data and provide that.

Cheers
Phil


The problem is the bold sentence. I dont want to have a range outside the chart (which i need if a use value from cell). The solution i am already using is the same you are suggesting (create a chart, use cube value outside the chart to retrieve data needed, use value from chart and select "cubevalue" range, but in this way i still need a "range" outside the chart).
I would like to obtain the same result without having a "support" range.
I think its not possible; Of course i could use a measure (delta%) directly in the chart but in this way the labels would follow axis values.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron