Forum Discussion
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(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! :)
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
5 Replies
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?
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$
