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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors