Forum Discussion

AllanBerces's avatar
AllanBerces
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Sumx per Category

Hi good afteroon,

Can anyone pls need assistance i want the total per catergory  on my table,how can i add Sumx function on my existing measure.

 

Output Measure =
VAR Available = SELECTEDVALUE('BRUNEI_WTI ARTICLES NO _sql'[Quantity])
VAR Required = SELECTEDVALUE('BRUNEI_WTI MATERIALS_sql'[ObjectNeeded])  
RETURN
    IF(
        Available = BLANK(),
        Required,
        IF(
            Available = Required,
            0,    
            IF(
                Available > Required,  
                0,
                Required - Available
            )
        )
    )

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, AllanBerces 

    You can try to use the following DAX:

    Output Measure =
VAR Available = SELECTEDVALUE('BRUNEI_WTI ARTICLES NO _sql'[Quantity])
VAR Required = SELECTEDVALUE('BRUNEI_WTI MATERIALS_sql'[ObjectNeeded])  
RETURN
    IF(
        Available = BLANK(),
        SUMX(
            FILTER(
                'BRUNEI_WTI MATERIALS_sql',
                'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] = Required
            ),
            'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded]
        ),
        IF(
            Available = Required,
            0,    
            IF(
                Available > Required,  
                0,
                SUMX(
                    FILTER(
                        'BRUNEI_WTI MATERIALS_sql',
                        'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] > Available
                    ),
                    'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] - Available
                )
            )
        )
    )

     

    Here are my thoughts. If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data).

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

2 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    AllanBerces 

     

    measure 1  =
VAR Available = SELECTEDVALUE('BRUNEI_WTI ARTICLES NO _sql'[Quantity])
VAR Required = SELECTEDVALUE('BRUNEI_WTI MATERIALS_sql'[ObjectNeeded])  
RETURN
    IF(
        Available = BLANK(),
        Required,
        IF(
            Available = Required,
            0,    
            IF(
                Available > Required,  
                0,
                Required - Available
            )
        )
    )


measure 2=  
sumx ( tbl_name[category] , [measure 1] )

     

     

    use measure 2 in your matrix rather than matrix measure 1 /. 

     

    let me know if this helps /

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🙏 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, AllanBerces 

    You can try to use the following DAX:

    Output Measure =
VAR Available = SELECTEDVALUE('BRUNEI_WTI ARTICLES NO _sql'[Quantity])
VAR Required = SELECTEDVALUE('BRUNEI_WTI MATERIALS_sql'[ObjectNeeded])  
RETURN
    IF(
        Available = BLANK(),
        SUMX(
            FILTER(
                'BRUNEI_WTI MATERIALS_sql',
                'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] = Required
            ),
            'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded]
        ),
        IF(
            Available = Required,
            0,    
            IF(
                Available > Required,  
                0,
                SUMX(
                    FILTER(
                        'BRUNEI_WTI MATERIALS_sql',
                        'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] > Available
                    ),
                    'BRUNEI_WTI MATERIALS_sql'[ObjectNeeded] - Available
                )
            )
        )
    )

     

    Here are my thoughts. If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data).

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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