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 moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
23 | |
22 |