ISBLANK return another value from table with different filter

[MCassady]

Hello,

I have a table that looks like below.

Date                   Value        DataType

2022/01/01         10             A

2022/01/02         15             A

2022/01/03         10             A

2022/02/01         11             A

2022/02/02         23             A

2022/02/03         10             A

2022/01/01         22             B

2022/01/02         55             B

2022/01/03         12             B

2022/02/01         73             B

2022/02/02         23             B

2022/02/03         14             B

2022/03/01         15             B

2022/03/02         23             B

2022/03/03         22             B

I need to return a MONTHLY SUM of the values, where if data type A exists, we use that number, however if A does not exist for the month, we use the total for data type B.

Expected results of the example above are:

MONTH      Value

Jan-22         35

Feb-22        44

Jan-22         60

As you can see, there is data for type A for the first 2 months, but for the 3rd, there is only type B, so we use type B.

I have created 2 measures, 1 to retrieve the data for type A (MeasureA) and one to retrieve type B (MeasureB).

For the purposes of my report, these are using filters to each get their respective datatype.

I have tried the following:

MeasureC = 

IF (ISBLANK([MeasureA]),[MeasureB],[MeasureA])

When I put measure C on a table visual, all the values appear by month properly, but the total at the bottom is only the sum of type A.

Example of the table visual:

MONTH      Value

Jan-22         35

Feb-22        44

Jan-22         60

TOTAL         79

Does anyone have any suggestions on another way to correctly do this?

@amitchandak you've been able to answer almost all of my questions. Hopefully you can help with this one! 🙂

[ghoshabhijeet]

@MCassady Thanks for sharing the problem.

I have one question on your sample data. What would be logic if data for both A and B datatype is available for the same month, like in the below snapshot.

[MCassady]

If both are available, we prioritize type A and ignore that B exists.

In sql it would be like having an ISNULL(A,B)

[Mikelytics · Accepted solution]

HI @MCassady ,

Please try the following:

Base data

in my example I assum you have a date table connected to your table

First the version with right rows and wrong total,. So if you do not need a total you can take this one, but then also turn off the total

AB_SUM = 

    var var_SumA =
        CALCULATE(
            SUM(SampleAB[Value]),
            SampleAB[DataType] = "A"
        )

    var var_SumB =

    CALCULATE(
            SUM(SampleAB[Value]),
            SampleAB[DataType] = "B"
        )

    RETURN

    if(
        NOT ISBLANK(var_SumA)
        ,var_SumA
        ,var_SumB
    )

If you also need the total then please take this formula

AB_SUM = 

SUMX(
    VALUES(DimDate[Year-Month]),

    var var_SumA =
        CALCULATE(
            SUM(SampleAB[Value]),
            SampleAB[DataType] = "A"
        )

    var var_SumB =

    CALCULATE(
            SUM(SampleAB[Value]),
            SampleAB[DataType] = "B"
        )

    RETURN

    if(
        NOT ISBLANK(var_SumA)
        ,var_SumA
        ,var_SumB
    )
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[MCassady]

@Mikelytics @amitchandak @Bifinity_75 

This seems to be the solution, I had the sumx in the wrong spot and double checked this post.

Thank you for the swift responses, you guys are the best!


Edit: Actually, it may be a long shot. But is there a way to do some conditional formatting to highlight which monthly values are from each datatype on the table? I have a feeling there isn't....

Example:

[amitchandak]

@MCassady , Please first refer to the reply by @Bifinity_75 , if that does not work try

sumx(Values('Date'[Month Year]), calculate(IF (ISBLANK([MeasureA]),[MeasureB],[MeasureA])) )

[Bifinity_75]

Hi @MCassady , try this measure:

Value = var A_= CALCULATE(sum('Table'[ Value ]),'Table'[DataType]="A")
var B_=CALCULATE(sum('Table'[ Value ]),'Table'[DataType]="B")
return
if (A_=BLANK(),B_,A_)

Best regards