Display Values in same Column and Aggregate dynamically

[Anonymous]

Currently have a DimProducts table with the following columns: Accounting Class, Product ID.

Also, I have a DimOrder table with the following column: Cancellation Reason

I need to bring the cancellation reason with the accounting class into one column and have the COUNT of product ID's adjust per the cancellation amounts.

For instance, I am going to create a stacked bar chart showing the number of products by type…and I need cancellations to show as one of the types(accountingclass).  So if accounting class had

Wrap                     50

Solo                      25

Preprint                10

and 5 of each were cancelled…

I would need it to show:

Wrap                     45

Solo                      20

Preprint                5

Cancellations       15

How can I handle this?

[v-juanli-msft · Accepted solution]

Hi @Anonymous

I don't know how to calcuate the "Cancellations" value.

So I make a test as "Cancellations"  is 5 by default.

1.could you create a column in your table to define "Cancellations"=5? if not, please let me know.

If you could, go on->

2.create a column of the count of product id, if you have problem doing this, let me know.

3.create columns  

remain = [count_product]-[cancellations]
sub = SUM(DimProducts[cancellations])
name = "Cancellations"

4.finally create a new table

Table 2 =
UNION (
    SUMMARIZE ( DimProducts, DimProducts[Accounting], DimProducts[remain] ),
    SUMMARIZE ( DimProducts, DimProducts[name], DimProducts[sub] )
)

If my formula doesn't work for your scenario, please share sone example datasets for me to test.

“

Community Support Team _ Maggie Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

“

Best regards,

Maggie

[Greg_Deckler]

Can you post a better representation of your source data? I'm not sure how your tables are related to one another. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Based on the current table structure there is no way to bring in the the SUM of CancellationReasons and add it as a row to the AccountingClass column. 

Ideally if I can bring in the Cancellation Reasons as it's own row with the sum of cancellations. As well, in the same table the COUNT to reflect dynamically the returns.

[Anonymous]