March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
20 |