Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
Chris123Terr
Frequent Visitor

Error in total crossing of rows and columns of a matrix

Chris123Terr_2-1677616196681.png

Medida 2 =
VAR TOTALXROW = SUMX(ADDCOLUMNS(SUMMARIZE(FACT_TABLE, DIM_PRODCATEGORY[ProductCategory]), "SUMA", CALCULATE(DISTINCTCOUNT(FACT_TABLE[SalesQuantity]))), [SUMA])

VAR TOTALXCOL = SUMX(ADDCOLUMNS(SUMMARIZE(FACT_TABLE, DIM_CALENDARIO[MONTH_TEXT_SHORT]),"SUMA", CALCULATE(DISTINCTCOUNT(FACT_TABLE[SalesQuantity]))), [SUMA])

VAR VALOR1 = DISTINCTCOUNT(FACT_TABLE[SalesQuantity])

RETURN

IF(HASONEFILTER(DIM_PRODCATEGORY[ProductCategory])=FALSE(), TOTALXROW, IF(HASONEFILTER(DIM_CALENDARIO[MONTH_TEXT_SHORT])=FALSE(), TOTALXCOL,
VALOR1))

Chris123Terr_3-1677616213807.png
I am using summarize to do the sum of the rows and the columns. But the total of the crossing of rows and columns comes out wrong.
What's going on?

 

 

2 REPLIES 2
DataInsights
Super User
Super User

@Chris123Terr,

 

I added SUMX to the result expression. The first argument needs to be the field in the visual (matrix rows).

 

Medida 2 =
VAR TOTALXROW =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( FACT_TABLE, DIM_PRODCATEGORY[ProductCategory] ),
            "SUMA", CALCULATE ( DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] ) )
        ),
        [SUMA]
    )
VAR TOTALXCOL =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( FACT_TABLE, DIM_CALENDARIO[MONTH_TEXT_SHORT] ),
            "SUMA", CALCULATE ( DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] ) )
        ),
        [SUMA]
    )
VAR VALOR1 =
    DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] )
RETURN
    SUMX (
        DIM_PRODCATEGORY[ProductCategory],
        IF (
            HASONEFILTER ( DIM_PRODCATEGORY[ProductCategory] ) = FALSE (),
            TOTALXROW,
            IF (
                HASONEFILTER ( DIM_CALENDARIO[MONTH_TEXT_SHORT] ) = FALSE (),
                TOTALXCOL,
                VALOR1
            )
        )
    )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Correction (omitted VALUES):

 

Medida 2 =
VAR TOTALXROW =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( FACT_TABLE, DIM_PRODCATEGORY[ProductCategory] ),
            "SUMA", CALCULATE ( DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] ) )
        ),
        [SUMA]
    )
VAR TOTALXCOL =
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( FACT_TABLE, DIM_CALENDARIO[MONTH_TEXT_SHORT] ),
            "SUMA", CALCULATE ( DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] ) )
        ),
        [SUMA]
    )
VAR VALOR1 =
    DISTINCTCOUNT ( FACT_TABLE[SalesQuantity] )
RETURN
    SUMX (
        VALUES ( DIM_PRODCATEGORY[ProductCategory] ),
        IF (
            HASONEFILTER ( DIM_PRODCATEGORY[ProductCategory] ) = FALSE (),
            TOTALXROW,
            IF (
                HASONEFILTER ( DIM_CALENDARIO[MONTH_TEXT_SHORT] ) = FALSE (),
                TOTALXCOL,
                VALOR1
            )
        )
    )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors