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

If all of your tables are linked correctly via the Relationships area of Power BI, you could make use of a Table or Matrix visual to do this.

 

For example, create a table visual and make the first field Articles.  This will mean that you get 1 row for each article.

Next field you want is the field that contains the data of whether it is Type 1 or Type 2.  From here, you will get 1 row for each Type each article has.

Lastly, make the 3rd field remenants.  Set this to summarise as Count.

 

Does this give you what you were after?

 

Unmake's avatar
Unmake
Regular Visitor
8 years ago

No, this way leads to summarise by article, but i need summarise  by measure.

Expected Result:

[type]   [count_articles]  [sum]

 

type1   111                     2323 $

type2   50                       70$

  • 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