Measures

[Khaoula_Farissi]

hello 

this is my data 

Id	BrandMonth1	BrandMonth2	Vague
1	A	D	3
2	B	D	1
3	C	D	1
4	D	C	2
5	A	A	3
6	B	B	1
7	C	D	2
8	A	C	2
9	B	D	2
10	C	D	1
11	D	D	1
12	D	C	3
13	D	A	2
14	C	B	3
15	A	D	1
16	B	C	2
17	D	C	2
18	C	A	2
19	A	B	3

i want to divide the percentage of BrandMonth1 by the percentage of BrandMonth2 and make Vague as columns and Brands as rows to get a table like this:

%BrandMonth1/%BrandMonth2	1	2	3
A	2.00		0.50
B	0.00		0.50
C	1.00	0.00	3.00
D	1.00	0.25	1.00

[Khaoula_Farissi]

Sorry for the misunderstanding

i want to calculate the count of every brand/total*100 this is how i do it in Excel:

count(BrandMonth1)/total(BrandMonth1)*100

count(BrandMonth2)/total(BrandMonth2)*100

i want to get :

[edhans · Accepted solution]

Hi @Khaoula_Farissi - I am not sure I have the formula right, but you can change it. I modeled this properly, so bear with me as I explain.

First, I created a dimension table with just the brands A, B, C, and D. So the model looks like this:

Brand and BrandMonth1 are active, Brand and BrandMonth2 are inactive. That is why one is dotted vs solid.

Then I created 3 measures:

Vague Brand 1 = 
DIVIDE(
    COUNTROWS('Table'),
    COUNTROWS(ALL('Table'))
) 
--------------------------------------------------------------


Vague Brand 2 = 
CALCULATE(
    DIVIDE(
        COUNTROWS('Table'),
        COUNTROWS(ALL('Table'))
    ) , 
    USERELATIONSHIP(Brand[Brand], 'Table'[BrandMonth2])
)
-------------------------------------------------------------


Vague Difference = [Vague Brand 1] - [Vague Brand 2]

Vague Brand 1 counts the rows for each row in the table (so all A rows, then all B rows, etc.) and divides that by all of the rows in the table. That is the %.

Vague Brand 2 does the same thing, but this time it uses the Brand/Brand Month 2 relationship. USERELATIONSHIP turns on that inactive relationship and turns off the Brand/Brand Month 1 for this measure only.

The Vague Difference is simply the differnece between the 2 measures. The results:

Here is my file so you can play with it as desired. If the math is wrong you can fix it or explain it better to me so I can assist.

[edhans]

Percentage of what @Khaoula_Farissi ? I don't see any values if you are saying [Vague] is your column headers. What is the math you want?