Forum Discussion
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
- v-henryk-mstfCommunity Support
Hi Veigar
Can you provide the type of test data (delete sensitive information) or the expected result, otherwise I cannot do further testing.
Looking forward to your reply.
Best Regards,
Henry- VeigarFrequent Visitor
Hi, Thanks for you answer.
Please find attached a sample of data, i cleaned them.
If you use the metric i posted you can then create some measure that count the rows, in order to count them.
https://drive.google.com/file/d/1tnQi7fNl87WpcT5UWZ-bevL1pzHw0nc-/view?usp=sharing