Forum Discussion

Veigar's avatar
Veigar
Frequent Visitor
5 years ago

Pie Chart: Return count element in a group

Hi everyone,
I have created this measure to calculate in which class of ABC Analysis an item is placed:

 

ABC WAREHOUSE = 

VAR T1 = SUMMARIZE(ALLSELECTED(ITEMS), ITEMS[ID], "QTY", [Average Stock])
VAR T2 = ADDCOLUMNS(T1, "QTY_CUMULATED", SUMX(FILTER(T1, [QTY] >= EARLIER([QTY])), [QTY]))
VAR QTY_TOT = SUMX(ALLSELECTED(ITEMS), [Average Stock])
VAR T3 = ADDCOLUMNS(T2, "PRC", DIVIDE([QTY_CUMULATED], QTY_TOT, 0))
VAR T4 = ADDCOLUMNS(T3, "ABC", SWITCH(TRUE(), [PRC] <= 0.8, "A", [PRC] <= 0.95, "B", [QTY] <> 0, "C",  "D"))
VAR T5 = FILTER(T4, ITEMS[ID] = MAX(ITEMS[ID]))
RETURN MAXX(T5, [ABC])

 

 
Average stock measure is defined :

 

Average Stock = 
AVERAGE(STOCKS[Qty])

 



My tables:
ITEMS[ID, Description]
STOCKS[Item, Qty]
SALES[Item, Value]

Both SALES[Item] and STOCKS[Item] are linked to ITEMS[ID].

So this measure returns, for example:
[ID]     [Class]
343      A
234      B
235      B
255      C
345      D

I want to count the number of items in every class (i.e. A = 1, B = 2, C = 1, D = 1) and return them. My aim is to put this data in a pie chart. I solved this problem by creating 4 different measures, but it's too time consuming. If it's possible, i want to keep the informations about the items in every class, so i can create a tooltip for other measures.

In the example above:

Pie chart with numbers of items, and if i point at one piece of the pie chart i want to see the value of all of the sales for every group. The tooltip is not the problem, i have problems in creating the measure to count every group and put them in a pie chart.

2 Replies