Forum Discussion

Unmake's avatar
Unmake
Regular Visitor
8 years ago
Solved

Group by text measures

I have two fact tables (sales and remnants) and two dimention tables (articles and time).   I calculate a text measure at articles table. It simple meaning:  measure  = if(sum(sales)-sum(remnant...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Unmake,

     

    It is not available to achieve your expected result exactly. We cannot summarize table based on a single measure, because different from a column, measure only returns a signle value without context. To make measure display a list of values, we should add a conext column in table visual.

     

    Suppose table structure is like:

     

    Please create measures similar to:

    sales total = SUM(Sales[Sales]) 
remnants total = SUM(remnants[Remnants])

Type = if([sales total]-[remnants total]>0,"type1","type2")

Count articles =
IF (
    [Type] = "type1",
    CALCULATE (
        COUNT ( articles[ArticleID] ),
        FILTER ( ALL ( articles ), [Type] = "type1" )
    ),
    CALCULATE (
        COUNT ( articles[ArticleID] ),
        FILTER ( ALL ( articles ), [Type] = "type2" )
    )
)

    Result.

     

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

What happens if you make a table exactly as you have shown in your reply?  First field Type, second field Articles (summaried as count), and third the value column you are expecting summaried as a sum? (you can use the same column multiple times) 

Unmake's avatar
Unmake
Regular Visitor
8 years ago

i cant move measure as row :( its main problem

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Unmake,

     

    It is not available to achieve your expected result exactly. We cannot summarize table based on a single measure, because different from a column, measure only returns a signle value without context. To make measure display a list of values, we should add a conext column in table visual.

     

    Suppose table structure is like:

     

    Please create measures similar to:

    sales total = SUM(Sales[Sales]) 
remnants total = SUM(remnants[Remnants])

Type = if([sales total]-[remnants total]>0,"type1","type2")

Count articles =
IF (
    [Type] = "type1",
    CALCULATE (
        COUNT ( articles[ArticleID] ),
        FILTER ( ALL ( articles ), [Type] = "type1" )
    ),
    CALCULATE (
        COUNT ( articles[ArticleID] ),
        FILTER ( ALL ( articles ), [Type] = "type2" )
    )
)

    Result.

     

    Best regards,

    Yuliana Gu