Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi all,
I have the following table:
Lots | Country | Type |
Lot 1 | Italy | Residential |
Lot 2 | Italy | Commercial |
Lot 3 | France | Commercial |
Lot 4 | Germany | Industrial |
Lot 5 | Switzerland | Other |
... | ... | ... |
I must create a matrix report of the following type:
Region | Residential | Commercial | Industrial | Other |
Italy | % of res. in Italy | % of comm. in Italy | % of ind. in Italy | % of other in Italy |
France | % ... | % ... | % ... | % ... |
Germany | ... | ... | ... | ... |
Switzerland | ... | ... | ... | ... |
... | ... | ... | ... | ... |
How can I calculate the percentages values with DAX?
Thanks.
Solved! Go to Solution.
Hey @Log2Ins,
Create the matrix with every region then for each percentage you need create a column like this:
In that case, I would create a second measure for the value,
And a second matrix atop the first.
Then, I would create to bookmarks- Value, Lots.
Set each bookmark to affect just the display of these two matrix tables, by choosing just them (use view>selection for that), hiding one and displaying the other in turn.
DIVIDE(
COUNT(Table[Lots]),
CALCULATE(
COUNT(Table[Lots]),
ALL(Table[Type])
)
)
This would return the % of each type (commercial, residential, etc...) in each country and in total.
Hi,
Juste create a Matrix with Type in columns and Country on lines and add this Measure to your matrix :
% per Type =
VAR CountType = COUNT( TableLot[Lots] )
VAR CountLots = CALCULATE( COUNT( TableLot[Lots] ) , ALL( TableLot[Type] ) )
RETURN
DIVIDE ( CountType, CountLots, 0 )
Hey @Log2Ins,
Create the matrix with every region then for each percentage you need create a column like this:
I would like to do something similar:
And if I would like to dynamically change the matrix based on different parameters?
For example, if the lots table has a column Value too and I would like to switch the percentage between the count and the value per region. Is it possible?
In that case, I would create a second measure for the value,
And a second matrix atop the first.
Then, I would create to bookmarks- Value, Lots.
Set each bookmark to affect just the display of these two matrix tables, by choosing just them (use view>selection for that), hiding one and displaying the other in turn.
I have another question: using the new region DIM, I lose the relationships with the other tables, except for the region name with the fact table, so the percentages are global and I can't filter them through the page filters. What's wrong?
I'll need to see how your model connects (tables and keys) to answer that.
In general, make sure that:
You were right again. Thank you!!
Thank you very much!!
Hi @Log2Ins not quite clear what percentage you want to see? Give a more detailed example
This is your desired result 1 or 2
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
24 | |
21 | |
21 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |