cancel
Showing results for
Did you mean: New Member

## Help with percentage measure with DAX

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.

2 ACCEPTED SOLUTIONS  Resolver II

Hey @Log2Ins,

Create the matrix with every region then for each percentage you need create a column like this:

Commercial =
Var nbr_residential = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
&&
'fact'[Type] == "Commercial"
))

Var nbr_total = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
))

Return (Divide(100 * nbr_residential,nbr_total))

Fact is ethe table with every "lots"
Dim the table with only regions

For other columns change the red text to mee your needs

Best regards,

Ps : I advice you to create a column nbr_total in order to not perform the variable calcultion nbr_total for each column but it is up to you ( this column would be :
nbr_total = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
))  Super User

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. -------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
12 REPLIES 12  Super User

``````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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!  Super User

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] ) )RETURNDIVIDE ( CountType, CountLots, 0 )`
And you'll get that : Hope it helps  Resolver II

Hey @Log2Ins,

Create the matrix with every region then for each percentage you need create a column like this:

Commercial =
Var nbr_residential = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
&&
'fact'[Type] == "Commercial"
))

Var nbr_total = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
))

Return (Divide(100 * nbr_residential,nbr_total))

Fact is ethe table with every "lots"
Dim the table with only regions

For other columns change the red text to mee your needs

Best regards,

Ps : I advice you to create a column nbr_total in order to not perform the variable calcultion nbr_total for each column but it is up to you ( this column would be :
nbr_total = CALCULATE(COUNTROWS('fact'),
Filter('fact',
dim[Region] == 'fact'[Country]
)) New Member

I would like to do something similar:

Residential =
Var nbr_residential = CALCULATE ( COUNTROWS ( Asset_Dim ),
Filter ( Asset_Dim,
pbi_Regions[Region] == Asset_Dim[Region]
&&
Asset_Dim[Type of property] == "Residential"
))
Var value_residential = CALCULATE ( sum ( Asset_Dim[Value] ),
Filter ( Asset_Dim,
pbi_Regions[Region] == Asset_Dim[Region]
&&
Asset_Dim[Type of property] == "Residential"
))
Return (IF(SELECTEDVALUE('Slicer'[Types])=="Value", DIVIDE(100*value_residential, pbi_Regions[value_total]), Divide ( 100 * nbr_residential , pbi_Regions[nbr_total] )))

But it seems selectedvalue doesn't work when I switch between Value and Count. New Member New Member

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?  Super User

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. -------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button! New Member

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?  Super User

I'll need to see how your model connects (tables and keys) to answer that.

In general, make sure that:

1. Each regionfield in any table connects to this Dim Region table
2. In any visual with region as a field (rows, columns, bars, filters, slicers...) use the Dim Region table, and not the individual region fields from fact tables.
-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button! New Member

You were right again. Thank you!! New Member

Thank you very much!!  Super User

Hi @Log2Ins  not quite clear what percentage you want to see? Give a more detailed example
This is your desired result 1 or 2 __________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine   