Forum Discussion
Measures
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 |
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.
3 Replies
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?
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 :
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.
