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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lulcasl
Frequent Visitor

How to calculate % of values within range?

Hello!

This is my first time posting here so, if you need further information I'll try to provide as much as possible.

I have a measure which is basically the weighted average of [Peso Líquido] x [Densidade].

DensidadePond = 
DIVIDE(
    SUMX( 'f_MovMadCons', [Peso Liquído] * [Densidade]),
    SUM ( 'f_MovMadCons'[Peso Liquído])
)

 

I need to check how many values are within a certain range, which is determined by another dimension table.

I created another measure to check if my [DensidadePond] values are within range or not. If they are, I get a 1 value, if it's not, 0.

 

Pontos Dentro = 
IF(isblank([DensidadePond]), blank(), IF(AND([DensidadePond]>=MAX('d_Limites de Consumo'[LIC]), [DensidadePond]<=MAX('d_Limites de Consumo'[LSC])), 1, 0))

 

This check is done hourly, so the results are basically this:

lulcasl_1-1605551505917.png

 

The first column is the hour column, there are missing values but those are intended.

So far, this works pretty much as intended, but theres still one more measure that I can't get to work.

I need to know how many hourly points are within said range (LIC/LSC) and how much % they represent.

This is the expected result I'm trying to achieve:

 

lulcasl_2-1605551973997.png

 

 

I want to know how many points where within range in a cumulative way, and how much of the % they represent.

Does anyone have any ideas on how I should approach this?

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @lulcasl 

It should be something along these lines, although I would need to know more about the field Hour and how the dates datetimes are organized in your model.

Measure =
VAR hours_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Hour] ),
        FILTER ( ALL ( Table1[Hour] ), Table1[Hour] <= MAX ( Table1[Hour] ) )
    )
VAR cumul_ =
    SUMX ( hours_, [Pontos Dentro] )
RETURN
    DIVIDE ( cumul_, COUNTROWS ( hours_ ) )

 

If you can share the pbix it would make things easier.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@lulcasl 

Variables are just containers where to store a result that can be used later. They are helpful mostly to simplify the code and make it more legible/maintainable. Now looking at the code of the measure:

1. We create a one-column table that has the hours relevant for the calculation (those previous or equal to the hour on the current row) and store it in the variable hours_ 

2. For each of those hours, we check how they have "Pontos dentro". The number is stored in cumul_

3. We divide cumul_ by the total number of relevant hours (COUNTROWS(hours_)) to get the percentage

 

Read some more about variables in DAX. They are easy to use and very useful. There's plenty of info online

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

AlB
Super User
Super User

Hi @lulcasl 

It should be something along these lines, although I would need to know more about the field Hour and how the dates datetimes are organized in your model.

Measure =
VAR hours_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Hour] ),
        FILTER ( ALL ( Table1[Hour] ), Table1[Hour] <= MAX ( Table1[Hour] ) )
    )
VAR cumul_ =
    SUMX ( hours_, [Pontos Dentro] )
RETURN
    DIVIDE ( cumul_, COUNTROWS ( hours_ ) )

 

If you can share the pbix it would make things easier.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

lulcasl
Frequent Visitor

Thanks @AlB , it worked perfectly!

lulcasl_0-1605613237727.png

 

Would you care to explain the thought process of the solution? I'm not acquainted with variables and I'm trying to learn about them.

 

Thanks in advance!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors