Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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))
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?
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
)
)
)
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
)
)
)
Proud to be a Super User!
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |