Total is not matching for SUMX/SUM.. Sample file attached

[Anonymous]

Hi
Need help with below requirement

https://drive.google.com/file/d/1EtWTRa6KNSekliI2pDYoyxqrCZwjr8Oq/view?usp=drive_link

I have a table with actuals for total volume, own volume, net revenue. The requirement is for total volume, we have to consider KPI "Total Volume" and for Own volume we have to consider, "Own Volume" kpi but for Australia even for Own Volume we need to take Total volume of australia. For "NR/HL", logic is divide(NR/Own Volume).

Could you please help me with the dax formula to get total volume for Australia in Own volume row

Total Volume= Total Volume of all countries
Own Volume= Own Volume of all countries but for Australia consider Total Volume 
Net Revenue= Net revenue of all countries
NR/HL= divide(NR,Own Volume)

I also need to see total.

Thanks in advance

[Anonymous]

Hi @Anonymous ,

It seems that I don't have the sufficient privilege to access your shared file. Could you please grant me the proper permission to access it? And you can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

https://drive.google.com/file/d/1EtWTRa6KNSekliI2pDYoyxqrCZwjr8Oq/view?usp=sharing

I have updated the permissions...Could you please check now

[Anonymous · Accepted solution]

Hi @Anonymous ，

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

Measure = 
VAR sel =
    SELECTEDVALUE ( 'KPI'[KPI] )
VAR selcountry =
    SELECTEDVALUE ( 'Data'[Country] )
VAR volume =
    DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Total Volume" ), 1000, 0 )
VAR ownvol1 =
    DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Own Volume" ), 1000, 0 )
VAR ownvol =
    IF (
        SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
        DIVIDE (
            CALCULATE (
                SUM ( Data[AC] ),
                FILTER ( ALLSELECTED ( Data ), Data[KPI] = "Own Volume" )
            ),
            1000,
            0
        ),
        ownvol1
    )
VAR nr =
    DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "NET REVENUE" ), 1000, 0 )
VAR nrhl =
    DIVIDE ( nr, ownvol, 0 ) * 1000
VAR result =
    IF (
        sel = "Total Volume",
        volume,
        IF (
            sel = "Own Volume",
            ownvol,
            IF (
                sel = "Net Revenue",
                nr,
                IF ( sel = "NR/HL", nrhl, volume + ownvol + nr + nrhl )
            )
        )
    )
RETURN
    result

2. Update the formula of measure [Actuals] as below

Actuals =
IF (
    SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
    [Measure],
    SUMX ( VALUES ( 'Data'[Country] ), SUMX ( VALUES ( 'KPI'[KPI] ), [Measure] ) )
)

Best Regards