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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.