Need help with formula

[Grahamwest]

Hello Folks,

I got an example table with:

Batch                           Qty (Kg)          Auction no.

A1025                           5                                1

A1025                           5                                2

A1026                           4                                2

A1025                           5                                3

A1027                           7                                3

A1025                           5                                4

A1027                           7                                4

Now I want a new calculated table with:

If I select Any of the auction let say 2 so result should be:

Batch                           Qty (Kg)         

A1025                           5                               

A1026                           4                               

But if I select multiple auctions let say 1,2 & 3 so result should be: 

Batch                           Qty (Kg)         

A1025                           5                               

A1026                           4                               

A1027                           7                               

Which means I should not sum up instead gives me the unique value of each auction.

How to achieve this?

Best regards,

Graham

[Anonymous]

Hi @Grahamwest,

Nope, power bi not support create calculated column/table based on slicer.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Regards,

XIaoxin Sheng

[Grahamwest]

Can it be done by using measure?

I have almost reached at the end, but now the porblem is totals. See this:

I tried doing it mathematically:

-TotalOfferedforunique = DIVIDE(SUM('Live Auction'[Offered QTY]),COUNT('Live Auction'[Batch No]))

-Distict Batch Count = DISTINCTCOUNT('Live Auction'[Batch No])

-Unique Offered QTY (MT) = [TotalOfferedforunique]*[Distict Batch Count]

Now the same formula is getting applied on its total, which I dont want. I want the sum of the columns.

When I am exporting it in excel and adding up it is giving me the unique value but not when I am seeing it in BI.

Thanks,

Graham

[Anonymous]

Hi @Grahamwest,

Actually, you only need to switch the summary mode, then it will to show the distinct value.

Notice: Summary mode 'Minimum', 'Maximum', 'Median', 'Average' suitable for your requirement.

Measure version:

Qty(Kg) = CALCULATE(LASTNONBLANK('sample'[ Qty (Kg)],[ Qty (Kg)]),ALLSELECTED('sample'[Auction no.]))

Regards,

Xiaoxin Sheng

[Grahamwest]

Hi @Anonymous

Thanks for the reply!!!

I need the sum of the totals: (5+4+7) = 16

Please help!!!

Thanks,

Graham

[Anonymous]

HI @Grahamwest,

You can use below measure to achieve your requirement, I modified formula to display total qty at 'Total'.

Qty(Kg) =
IF (
    ISFILTERED ( 'sample'[Batch] ),
    CALCULATE (
        LASTNONBLANK ( 'sample'[ Qty (Kg)], [ Qty (Kg)] ),
        ALLSELECTED ( 'sample'[Auction no.] )
    ),
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( 'sample' ),
            'sample'[Batch],
            "QTY", DISTINCT ( 'sample'[ Qty (Kg)] )
        ),
        [QTY]
    )
)

Regards,

Xiaoxin Sheng

[Grahamwest]

Hi, @Anonymous

You are awesome!!! This is what I wanted. Thanks a lot for your help.

Just a slight problem, when I am selecting 2 auctions which have unique value/no duplicate value it is giving me below error:

please help me on this too.

Also I would like to understand the formula what it means.

Thanks again!!!

Thanks,

Graham

[Grahamwest]

Also,

when i am selecting all of the auctions it is giving me the same value:

It is working fine if i am not taking any attribute in a column.

Thanks,

Graham

[Anonymous]

Hi @Grahamwest,

Based on test, this formula works when I test to choose multiple unique records in slicer.

Have you modified the group column or slicer column? If not, please share the sample pbix file to test.

BTW, I also update the formula condition to confirm it can correct filter the total row, maybe you can try it.

Qty(Kg) = 
IF (
    COUNTROWS ( 'sample' ) <> COUNTROWS ( ALLSELECTED ( 'sample' ) ),
    CALCULATE (
        LASTNONBLANK ( 'sample'[ Qty (Kg)], [ Qty (Kg)] ),
        ALLSELECTED ( 'sample'[Auction no.] )
    ),
    SUMX (
        SUMMARIZE (
            ALLSELECTED ( 'sample' ),
            'sample'[Batch],
            "QTY", DISTINCT ( 'sample'[ Qty (Kg)] )
        ),
        [QTY]
    )
)

Regards,

XIaoxin Sheng

[Grahamwest]

Hi @Anonymous

I have tried this one too, but it is giving me same result,

Can I have you email Id so that I can send you the data and that may be esier for you to work upon?

Thanks,

Graham

[Anonymous]

Hi @Grahamwest,

You can share the sample link with private message, I will check and test on it.

Regards,

Xiaoxin Sheng