Need help visualizing calculation already placed as data in field.

[Anonymous]

Hi BI Community, 

The context is I have a team member that submits me a variety of supply chain data, and in simple terms, can be separated into KPI name + date (monthly) + value. The member submits both raw data and calculations under the "KPI name" field, for example: (customer service level = delivered/requested) as percentage.

KPI Name	Date	Value
Items requested	1/1/2021	10
Items requested	2/1/2021	10
Items delivered	1/1/2021	9
Items delivered	2/1/2021	10
CSL	1/1/2021	0.9
CSL	2/1/2021	1

This is just one example, but in fact there are many KPIs under the "KPI Name" field. My team cascades one field with raw data + calculation for all of our KPIs.

The issues i encountered, when building a cluster column + line combo chart, are:

1) The CSL line values are in decimal form when i want percentage. I managed to do a workaround by by pivoting CSL to a separate column where only percentage values are represented.

2) more importantly, when i move the shared axis up to quarter or year hierarchy level, the CSLs sum each other where instead I want to to have a value represented by the sums of the quarterly and yearly delivered/requested formulation. I understand this is due to CSL values represented as columns, not measures, and thus do not adapt from slicers or filters.

Is there a measure or formula i can implement to get CSL numbers to show properly when i filter the date shared axis? My initial thought is to have items requested and items delivered as separate columns, but the visual would allow me to have 2+ column series. Thanks in advance!

Ref:

[Anonymous]

I Agree. I think better to have only raw data consisting of inputs and any calculations used as DAX measure instead of including it in raw data itself. Seems to work for now!

[Anonymous]

Hi @Anonymous ,

I'm not quite sure if I understand your needs accurately. Would you mind telling me a more detailed description of your needs so that I can help you better?

Sample data without sensitive information and expected output would help tremendously.

Please see this post regarding: How to get your question answered quickly 

Hope it helps,

Community Support Team _ Caitlyn

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

[lbendlin · Accepted solution]

Sounds like you want to explore using Calculation Groups?