Problem to create table and graph

[Anonymous]

Hello

Can you try inputting the following data please in a pbix and help me overcome the issues?

Table1:

ID; CATEGORY;
A1; A;
A2; A;
A3; A;
B1; B;
B2; B;

Table2:
ID; NUMBER OF ITEMS
A1; 1
A2; 10
A3; 3
B1; 2
B2; 1

I want to create a table visual in the following format:

CATEGORY; ID; TOTAL ITEMS

The TOTAL ITEMS is the following MEASURE:

TOTAL ITEMS = IF(SELECTEDVALUE(Table1[CATEGORY])="A",
CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[ID]=SELECTEDVALUE(Table1[ID])),Table2[NUMBER OF ITEMS]<>1),
IF(SELECTEDVALUE(Table1[CATEGORY])="B",
CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[ID]=SELECTEDVALUE(Table1[ID])),Table2[NUMBER OF ITEMS]=<>2),
BLANK()))

So the result in the table visual should be:

CATEGORY; ID; TOTAL ITEMS

A; A1; 0/blank

A; A2; 1

A; A3; 1

B; B1; 0/blank

B; B2;  1

I also want to create a graph that will show in x axis the Category and in y axis the SUM of the TOTAL ITEMS as displayed in the table visual above (CATEGORY; ID; TOTAL ITEMS).

So the values in the graph would be:

bar1 = A and 2

bar2 = B and 1 

Thanks!

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create [SUM of the TOTAL ITEMS] using measure like DAX below, and put it into Values box of chart visual.

SUM of the TOTAL ITEMS= SUMX(VALUES([CATEGORY]), [TOTAL ITEMS] )

Best Regards,

Amy 

Community Support Team _ Amy

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

[parry2k]

@Anonymous logic for Total Items is not clear, can you explain the logic how you are getting to those numbers, what is the business logic?

[Anonymous]

Hello @parry2k 

Sorry about that. I did not put the names of the columns/measures to represent my business case for anonymity.

Please find the pbix here: https://ufile.io/pek51r0j (I posted to another thread but I suppose you will only look here)

What I want to create is a graph with y axis to be the KPIs and y axis the SUM of the NO OF TRANSACTIONS.

Can you tell me please how to do that? Thanks!