Help with percentage measure with DAX

[Log2Ins]

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.

[philouduv · Accepted solution]

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]

))

) 

[rbriga]

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.

[AilleryO]

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 )

And you'll get that :

Hope it helps

[philouduv · Accepted solution]

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]

))

) 

[Log2Ins]

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.

[Log2Ins]

I opened a new post: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-use-SELECTEDVALUE-into-DAX/m-p/2504629...

[Log2Ins]

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?

[rbriga · Accepted solution]

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.

[Log2Ins]

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?

[rbriga]

 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.

[Log2Ins]

You were right again. Thank you!!

[Log2Ins]

Thank you very much!!

[DimaMD]

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