Create a bar chart from table where it's the result of a division of filtered counts

[Mumiah]

Hey all, hopefully you can help me, I'm trying to figure out this complex (at least for me) output, basically I have a table called GTSSD and for the sake of simplicity has columns _Ticket Number, OR, AR. I need to output in a bar chart a percentage result of the division of AR by OR I need to be able to add filters to both AR and OR but I'm trying to keep it simple for now to figure out the logic, I have a working formula if I would just need the Grand Total division but I need the measure  to contemplate where the division is A by A, B by B, C by C, D by D and exclude XX

Working formula

SAMPLE_FORMULA = 

DIVIDE
(

CALCULATE(COUNT('GTSSD'[_Ticket Number]), NOT('GTSSD'[OR] IN {"Other"}))

,

CALCULATE(COUNT('GTSSD Tickets'[_Ticket Number]), NOT('GTSSD Tickets'[AR] IN {"Other"}))

)

These are sample values and sample outcome 

AR Column	Count
A	9
B	9
C	15
D	11
Grand Total	44
OR Column	Count
A	10
B	15
C	20
D	25
Grand Total	70
SAMPLE expected Output Table	%
A	90%
B	60%
C	75%
D	44%
Grand Total	63%

Actual look I'm going for but not related to the sample data from above

Again any help is truly appreciated is I had a working measure when I had a direct connection to the database but as soon as I switched to Import data for some reason that broke it.

[Ashish_Mathur]

Hi,

Create a dimension tablw which has A,B,C,D.  Create a relationship (Many to One and Single) from the first column of both tables to the Dimension table.  To your visual, drag the column from the Dimesnion table.  Write these measures

AR count = countrows('ar table')

OR count = countrows('or table')

Measure = divide([ar count],[or count])

Hope this helps.

[Mumiah]

So in this sample the table has 3 columns Column 1 is _Ticket Number, column 2 is  AR and column 3 is OR.

Below I've created also a sample data, what I showcased above was the count of values  for each  Column , but hopefully with the data below it'll make more sense, again I appreciate your assistance. 

Column1	Column2	Column3
_ticket Number	AR	OR
1	A	A
2	A	A
3	A	A
4	A	A
5	A	A
6	A	A
7	A	A
8	A	A
9	A	A
10	B	A
11	B	B
12	B	B
13	B	B
14	B	B
15	B	B
16	B	B
17	B	B
18	B	B
19	C	B
20	C	B
21	C	B
22	C	B
23	C	B
24	C	B
25	C	B
26	C	C
27	C	C
28	C	C
29	C	C
30	C	C
31	C	C
32	C	C
33	C	C
34	D	C
35	D	C
36	D	C
37	D	C
38	D	C
39	D	C
40	D	C
41	D	C
42	D	C
43	D	C
44	D	C
45	XX	C
46	XX	D
47	XX	D
48	XX	D
49	XX	D
50	XX	D
51	XX	D
52	XX	D
53	XX	D
54	XX	D
55	XX	D
56	XX	D
57	XX	D
58	XX	D
59	XX	D
60	XX	D
61	XX	D
62	XX	D
63	XX	D
64	XX	D
65	XX	D
66	XX	D
67	XX	D
68	XX	D
69	XX	D
70	XX	D
71	XX	XX
72	XX	XX
73	XX	XX
74	XX	XX
75	XX	XX

[Irwan]

hello @Mumiah 

please check if this accomodate your need.

1. unpivot your table

2. create a new table consisted of your AR/OR value. You can use summarize to AR or OR column whichever has the most value, but i just inputed manually because only A,B,C,D.

3. create a new measure with following DAX.

Sample_formula =
var _OR = 
COUNTX(
    FILTER(
        'Table',
        'Table'[Attribute]="OR"&&
        ('Table'[Value]="A"||'Table'[Value]="B"||'Table'[Value]="C"||'Table'[Value]="D")
    ),
    'Table'[_ticket Number]
)
var _AR = 
COUNTX(
    FILTER(
        'Table',
        'Table'[Attribute]="AR"&&
        ('Table'[Value]="A"||'Table'[Value]="B"||'Table'[Value]="C"||'Table'[Value]="D")
    ),
    'Table'[_ticket Number]
)
Return
DIVIDE(
    _AR,
    _OR
)

4. plot in table visual. Take ID from the previous ID table.

 Hope this will help.

Thank you.

[Mumiah]

So the data I provided in my previous very long post is the raw data there's not attribute and value  columns and the _Ticket number are unique. Regardless of that discrepancy, I tried tweaking the formula you provided, created the ID table but seems still only accurately calculates the total percentage which I had no problem with, basically I need the output table to be the AR A divided by OR A , AR B by OR B, etc, hopefully this clarifies a bit what I'm struggling with

ticket Number	AR	OR
1	A	A
2	A	A
3	A	A
4	A	A
5	A	A
6	A	A
7	A	A
8	A	A
9	A	A
10	B	A
11	B	B
12	B	B
13	B	B
14	B	B
15	B	B
16	B	B
17	B	B
18	B	B
19	C	B
20	C	B
21	C	B
22	C	B
23	C	B
24	C	B
25	C	B
26	C	C
27	C	C
28	C	C
29	C	C
30	C	C
31	C	C
32	C	C
33	C	C
34	D	C
35	D	C
36	D	C
37	D	C
38	D	C
39	D	C
40	D	C
41	D	C
42	D	C
43	D	C
44	D	C
45	XX	C
46	XX	D
47	XX	D
48	XX	D
49	XX	D
50	XX	D
51	XX	D
52	XX	D
53	XX	D
54	XX	D
55	XX	D
56	XX	D
57	XX	D
58	XX	D
59	XX	D
60	XX	D
61	XX	D
62	XX	D
63	XX	D
64	XX	D
65	XX	D
66	XX	D
67	XX	D
68	XX	D
69	XX	D
70	XX	D
71	XX	XX
72	XX	XX
73	XX	XX
74	XX	XX
75	XX	XX

[Irwan]

hello @Mumiah 

attribute and value columns come from unpivot AR and OR column.

Thank you.

[Mumiah]

Hey Irawn again thanks for all your help, while this works with the very basic sample data I provided I can't use it as an approachable method as my table roughly other 50 other columns  that depend the source table not being modified as it would affect all other charts within the dashboard, do you have any ideas on how I can achieve this without adding steps in the advanced editor?

[Mumiah · Accepted solution]

So I was able to accomplish my goal by creating a separate table called 'Table_2' that contains the possible AR and OR values (A,B,C,D,Other) under column [Letter] and referencing it in the measure below, then build a bar chart where the [Letter] is the X Axis and the Measure below is the Y axis. But again thanks a lot @Irwan for your assistance on this issue really appreciate it.

Measure = 
VAR AR_Tickets = 
    CALCULATE(
        COUNT('table'[_Ticket Number]),
        FILTER('table',
            
            'table'[OR] = SELECTEDVALUE('Table_2'[Letter])
        )
    )
VAR OR_Tickets = 
    CALCULATE(
        COUNT('table'[_Ticket Number]),
        FILTER('table',
            
            'table'[OR] = SELECTEDVALUE('Table_2'[Letter])
        )
    )
RETURN
IF(OR_Tickets = 0, BLANK(), DIVIDE(AR_Tickets, OR_Tickets))

[Irwan]

hello @Mumiah 

i assumed the grand total coming from total option (not a row value).

create a new measure with following DAX.

Sample_formula =
var _OR = 
SUMX(
    'OR',
    'OR'[Count]
)
var _AR =
SUMX(
    VALUES('AR'),
    'AR'[Count]
)
Return
DIVIDE(
    _AR,
    _OR
)

dont forget to create a relationship between AR and OR table.

Hope this will help.

Thank you.

[Mumiah]

Hey Irwan thanks for your response but there are not 2 tables 'AR' and 'OR' are just columns from the same GTSSD table.

[Irwan]

hello @Mumiah 

then how to differentiate which one is AR or OR if they are coming from one table?

is there another column that define AR or OR?

Thank you.