Another way to count occurences of a measure

[Slim-]

Hello everybody,

I am currently struggling to implemant a "causal analysis" and I would like to hear your tips, any idea would be greatly appreciated.

First let me explain :

I have a table containing all production data for years. Each line is a transformation of a product. A product may be called many times in the table if it is transformed many times.

I have implemented an historical stock movement in a measure (I hid few column and measure name for confidentiality) :

Historique_Stocks = 
CALCULATE(
	SUM('Calendrier'[         ]),
	FILTER(
		ALLSELECTED('Calendrier'[Date]),
		ISONORAFTER('Calendrier'[Date], MAX('Calendrier'[Date]), DESC)
	)
)
-
CALCULATE(
	SUM('Calendrier'[      ]),
	FILTER(
		ALLSELECTED('Calendrier'[Date]),
		ISONORAFTER('Calendrier'[Date], MAX('Calendrier'[Date]), DESC)
	)
)
+
CALCULATE(
	SUM(Calendrier[     ]),
	FILTER(
		ALLSELECTED('Calendrier'[Date]),
		ISONORAFTER('Calendrier'[Date], MAX('Calendrier'[Date]), DESC)
	)
)

 This measure is reporting the evolution of stock, here is what can be displayed with it :

Then in a drillthrough I have a matrix summarizing each day of the month selected, with :

Column 1 Date

Column 2 Deviation from the objective

Column 3 Tons

Column 4 Stock available in the morning

Column 5 A justification of the deviation if known :

It can be "breakdown", "not enough stocks" etc.

My issue is with the measure "Justification" : it is just a lot of IF (for example, if stock < objectives then "not enough stocks" etc.). But is has to be a measure because most of the data compared in it are complex measures using "calculate" (as Historique_Stocks shown above) and therefore not doable in a calculated column.

I would like to print a pareto to plot the top of "Justification" and number of occurences in the drillthrough, but cannot do it for now since it is a measure. Any idea to change my set and plot this pareto ?

Thanks a lot for your help,

Regards

[v-kongfanf-msft]

Hi @Slim- ,

As you say, it can be tricky to plot Pareto charts directly using metrics. Try creating a calculated table that aggregates the data at the granularity required for a Pareto chart.

Best Regards,
Adamk Kong