cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Log2Ins
New Member

Help with percentage measure with DAX

Hi all,

 

I have the following table:

 

LotsCountryType
Lot 1ItalyResidential
Lot 2ItalyCommercial
Lot 3FranceCommercial
Lot 4GermanyIndustrial
Lot 5SwitzerlandOther
.........

 

I must create a matrix report of the following type:

 

RegionResidentialCommercialIndustrialOther
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
philouduv
Resolver II
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]
))

View solution in original post

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.Bookmarks.png

 

 

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

View solution in original post

12 REPLIES 12
rbriga
Super User
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!
AilleryO
Super User
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] ) )
RETURN
DIVIDE ( CountType, CountLots, 0 )
And you'll get that :
PourcentageType.png
Hope it helps
philouduv
Resolver II
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]
))

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.

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.Bookmarks.png

 

 

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

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:

  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!

You were right again. Thank you!!

Thank you very much!!

DimaMD
Super User
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 2InkedScreenshot_10_LI.jpg


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.