topic Help with percentage measure with DAX in DAX Commands and Tips

Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 08:42:51 GMT

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.

Re: Help with percentage measure with DAX

DimaMD — Tue, 10 May 2022 09:13:13 GMT

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

Re: Help with percentage measure with DAX

philouduv — Tue, 10 May 2022 08:59:13 GMT

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]

))

)

Re: Help with percentage measure with DAX

AilleryO — Tue, 10 May 2022 09:06:43 GMT

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

Re: Help with percentage measure with DAX

rbriga — Tue, 10 May 2022 09:21:15 GMT

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.

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 09:43:47 GMT

Thank you very much!!

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 10:06:45 GMT

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?

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 10:58:11 GMT

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.

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 11:16:34 GMT

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

Re: Help with percentage measure with DAX

rbriga — Tue, 10 May 2022 12:01:08 GMT

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.

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 13:09:13 GMT

You were right again. Thank you!!

Re: Help with percentage measure with DAX

Log2Ins — Tue, 10 May 2022 15:03:16 GMT

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?

Re: Help with percentage measure with DAX

rbriga — Wed, 11 May 2022 07:59:44 GMT

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

In general, make sure that:

Each regionfield in any table connects to this Dim Region table
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.