Group by text measures

[Unmake]

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(remnants)>0:"type1";"type2")

When i change date period on dateslicer  - value of my measure changes - it's ok.

But i dont understand how can i calculate how much articles have "type1" and how much have "type2" and how can i visualize this. 
+ need to calculate sum(remnants) by measure

I hope you help me! 🙂

[Anonymous]

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]

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]

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]

i cant move measure as row 😞 its main problem

[v-yulgu-msft · Accepted solution]

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