Dax Calculation Fill down and Fill Up

DAX DAX Calculations
1

Hi ALL,

Use Case -
Percentage Value should be calculated based on the max value for that category( this seems to be easy we can use allexcept by category in denominator and get percentage).
The max value criteria is > 800
However here the challenge is , consider u are in Mar 22 the value comes as 888 for Jan 22, Feb 22 the value is 400 , 200 so till Mar 22 the calc should be 400/888 and 200/888 however in Apr the value is again low less than 800 i.e 600 so for Apr it would be 600/888. Now for May 22 the value comes as 1234 so hence forth for the further month until a new value greater than 800 comes in the deno should be 1234 only the 888 values should get freezed till apr 22. Excel screen shot below
Snap 1

I am also attaching the pbix with data
@AntrikshSharma @BrianJ - It would be great if you provide your valuable guidance here
Sample data.xlsx (10.6 KB)
Test mvno.pbix (128.1 KB)

2

Hello @Vishy,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below is the measure alongwith the screenshot of the final results provided for the reference -

Output =
VAR _Numerator =
[Total Sales]

VAR _Previous_Best_Sales_When_Total_Sales_Is_Less_Than_800 =
MINX(
    FILTER( ALLEXCEPT( Data , Data[Category] ) ,
        [Total Sales] > 800 ) ,
    [Total Sales] )

VAR _Previous_Best_Sales_When_Total_Sales_Is_More_Than_800 =
CALCULATE(
    MAXX(
        FILTER( ALLSELECTED( Dates ) ,
            Dates[Date] <= MAX( Dates[Date] ) ) ,
        [Total Sales] ) ,
    ALLEXCEPT( Data , Data[Category] , Dates[Date] ) )

VAR _Denominator =
IF( _Previous_Best_Sales_When_Total_Sales_Is_More_Than_800 <= 800 ,
    _Previous_Best_Sales_When_Total_Sales_Is_Less_Than_800 ,
    _Previous_Best_Sales_When_Total_Sales_Is_More_Than_800 )

VAR _Results =
DIVIDE(
    _Numerator ,
    _Denominator ,
    0 )

RETURN
IF( ISBLANK( [Total Sales] ) ||
    NOT ISINSCOPE( Data[Category] ) ,
    BLANK() ,
    _Results )

Final Results
Final Results760×552 37.9 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Determining Best Sales - Harsh.pbix (63.9 KB)

3 Likes
3

Hey Harsh,

The solution seems to be working so smooth , thanks so so much

4

@Harsh - I came across a scenario where this logic would fail i am attaching the updated pbix with updated dataset
EDNA Solution - 1 - Harsh.pbix (66.4 KB)

5

Hello @Vishy,

The solution is provided based on the scenario presented in the original thread.

Please create a new thread for your new scenarios. As per the Forum Guidelines, one question can be posted per thread. Please go through the guidelines for the reference.

Thanks and Warm Regards,
Harsh

6

@Harsh - Agreed , I have created another thread and marked you as well hope it helps. Have given two data set scenario