Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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?
Solved! Go to Solution.
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
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
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
Thanks @AlB , it worked perfectly!
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!
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |