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 III
Resolver III

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 III
Resolver III

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

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors