Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPercentage 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting