Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Urgent- Simple formula but complex issue

Hello,

 

I would like to express the column " % as of NNS "( which is simply Num / Den)  in function of the the category . However when I do put the value in a graphe it will simply sum the two value that we have for VMHS  % 24 % + 64% = 88 % ( screen 1) which are not correct because MHS Professional has much lower value than VMHS VP ( not taking the weight into consideration) ( screen 2 for weight Num , Den columns) .

 

 

 

 

I would like to create another column which takes only categories , for exemple it will give us 

Num for VMHS  = 608 + 12 

Den for VMhs = 50 + 983 

% as of NNS = (608 + 12 ) / ( 50 + 983 ) =   61 % and NOT 88%

such that when we do use a graphe using category ( Y Axis ) and % as of NNS as (X axis ) it will give us the right values.

It  does not aggreagte correctly 

 

 

data protection
Need Help
Show and Tell
Tips and Tricks
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Can you share the data by text or table than screenshot.

     

    Or you can try this dax to do that:

    Column =
VAR _1 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Num] ), SUM ( 'Table'[Den] ) ),
        FILTER (
            'Table',
            [KPI] = EARLIER ( 'Table'[KPI] )
                && [Period Type] = EARLIER ( 'Table'[Period Type] )
                && [Cluster] = EARLIER ( 'Table'[Cluster] )
                && [category] = "vmhs"
        )
    )
VAR _2 =
    DIVIDE ( [Num], [Den] )
RETURN
    IF ( [category] = "vmhs", _1, _2 )

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Can you share the data by text or table than screenshot.

     

    Or you can try this dax to do that:

    Column =
VAR _1 =
    CALCULATE (
        DIVIDE ( SUM ( 'Table'[Num] ), SUM ( 'Table'[Den] ) ),
        FILTER (
            'Table',
            [KPI] = EARLIER ( 'Table'[KPI] )
                && [Period Type] = EARLIER ( 'Table'[Period Type] )
                && [Cluster] = EARLIER ( 'Table'[Cluster] )
                && [category] = "vmhs"
        )
    )
VAR _2 =
    DIVIDE ( [Num], [Den] )
RETURN
    IF ( [category] = "vmhs", _1, _2 )

     

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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