Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

rbriga
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

 

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
Memorable Member
Memorable Member

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?

 

rbriga
Impactful Individual
Impactful Individual

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?

rbriga
Impactful Individual
Impactful Individual

 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
Solution Sage
Solution Sage

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors