Measure to show as new row on certain filter value

[Sky1]

Hi,

I am still new to PowerBI. I use a lot of Excel daily to work. I need advice from you guys on whether this is possible or not.

So Signify Supplier needs to have 3% packaging fee, while the other supplier doesn't need it. I already created a measure to only calculate this function and it is working as expected. Would it be possible to calculate this 3% as an extra row, not as an extra column beside the Total Order Value column?

 

The goal:

 

When Signify is not selected as the supplier name, there won't be any packaging fee on the products, which the table below is already correct, however just like the request above, I'd like to not show the Signify Packaging Fee column if possible.

Many thanks. Hopefully it's not confusing.

[Sky1]

Signify Fee column is counted when the supplier = Signify and it's a 3.3% from the Order Value column.
I have made a measure to calculate this column:

Signify Packaging Fee = CALCULATE(sum(Orders[Total value])*0.033,KEEPFILTERS((Orders[Supplier]="Signify")))

Current result 

School Name	Supplier Name	Product Code	Qty	Order Value	Signify Fee
A	Signify	FT100	1	100	3.3
B	Signify	LA1	2	500	16.5
C	Signify	LA2	3	600	19.8
D	Signify	LA3	4	400	13.2
Total			10	1600	52.8

Expected result

School Name	Supplier Name	Product Code	Qty	Order Value
A	Signify	FT100	1	100
B	Signify	LA1	2	500
C	Signify	LA2	3	600
D	Signify	LA3	4	400
Signify Fee				52.8

[lbendlin]

Something's still off with the display but you see the general idea.

[Sky1]

I tried to recreate the solution however it doesn't seem to work. I have matched the relationship between the tables as well. Not sure where this goes wrong?

Update: sorry wrong screenshot before

[lbendlin]

You need to aggregate Supplier and Product

[Sky1]

Aggregating those fields removed the other products and the calculation is still not calculating correctly.

Previous visual before aggregate:

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[ghaines]

Put simply, the column you use in the visual for "School Name" needs to contain "Signify Packaging Fee". You could create a new table using DAX using UNION across the School Data table and a declared table that just contains the Signify row, or you could add a new row during data load in power query.  After lbendlin's reply to my previous comment, I'd prefer the DAX approach.  After the table is created you could add a new column that is equal to the school name when not Signify, and "Zzzz" when it is, and then sort School Name by that column.

When the new School Name column is used in the visual, the row context of [School Name] = "Signify Packaging Fee" is available for the measure to run correctly.

[Sky1]

Hi Ghaines, appreciate your answer however I am still quite new to PowerBI and not sure how to implement the guide you provided above. I'll try the Power Query solution and see if that works

[ghaines]

So you'd also need to built out the Schools table to include Signify Fee, probably during power query?  Also best to sort the schools by school ID if there is such a thing with Signify Fee being arbitrarily high to place it at the bottom, or add an index during load and use that to sort columns.

[lbendlin]

You can do it in Power Query or via a calculated table (UNION() ).  fair point on the sorting.

[lbendlin]

You can show/hide that row as part of the standard rows. You can't add it as a second total.

[Sky1]

Thank you for your reply. How do you show / hide it as part of the standard rows?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...