% to Sales Profit and Loss in Matrix Visual

[Anonymous]

Hi, First time posting - trying to use PowerBI in my workplace but finding it difficult to learn the DAX language and logic.

Hoping someone can help me.

I've got my data set up for the General Ledger accounts and stacked the data with Actuals and Budget in along with months and stores...

What i am struggling to do is divide all the entries in the matrix visual by "accounting sales" to get the classic % to Sales values.

I've madea  simple powerbi report which shows this but unsure how to add it...

I cant seem to get the matrix to return the sum of "Sales" for all values regardless of the breakdown - tried the ALL() function but i think its something to do with the fact that the Sales are linked to that table and i need them to be disconnected or something?

hoping someone can help...

if anyone can tell me how to upload a powerBI file i'll happily share the example...

Thanks!!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

You can refer to the formula.

Measure = 
// the sales of "Accounting sales"
VAR x = SUMX( FILTER( ALL(Sheet1), Sheet1[Level1] = "Accounting Sales" ), [Sum sales] )
//The [Sum sales] in my model is a column.
VAR y = SUM(Sheet1[Sum sales])
RETURN
DIVIDE(
    y, x, 0
)

If it can not solve your problem, please upload your .pbix file through OneDrive for Business.

Best regards,
Lionel Chen

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

[amitchandak]

@Anonymous , Try Measure like

divide(calculate(sum(Table[Value]), table[GL_Code]="Sales"),sum(Table[Value]))

Make it a % type column

[Anonymous]

Hi Amitchandak, Thanks so much for your help so far... i'm not sure why i've not been able to get that to work so far, I tried calculate and filter() but I assume using without the filter function treats it more like a "Sumif" in excel...

So far i get this...

Is there any way of getting the % to Accounting sales to flow down the sub cats... so COGS 1 would show 14.3% and Cogs 2 would show 5.7%?

Thanks again for your help - been driving me crazy!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

You can refer to the formula.

Measure = 
// the sales of "Accounting sales"
VAR x = SUMX( FILTER( ALL(Sheet1), Sheet1[Level1] = "Accounting Sales" ), [Sum sales] )
//The [Sum sales] in my model is a column.
VAR y = SUM(Sheet1[Sum sales])
RETURN
DIVIDE(
    y, x, 0
)

If it can not solve your problem, please upload your .pbix file through OneDrive for Business.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft ,

thanks for the below - i'm still not able to get the result i need though, sorry.

thanks for the one drive - hopefully this works...

https://grandvision-my.sharepoint.com/:u:/r/personal/scott_evans_visionexpress_com/Documents/TEST.pb... 

Thanks again!

[v-lionel-msft]

Hi @Anonymous ,

I can't access your link. You can directly paste the data from these two tables in the reply in the form of a table.

Similar to this, it is convenient for me to copy.

Date	ID
01/01/19	1
01/02/19	1
01/03/19	1
01/04/19	1
01/05/19	1
01/06/19	1
01/07/19	1
01/08/19	1
01/09/19	1
01/10/19	1
01/11/19	1

Best regards,
Lionel Chen

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

[Anonymous]

Date	Store	GL_Code	Value
01/12/2018	101	Sales	120
01/12/2018	101	Discounts	-20
01/12/2018	101	COGs 1	-15
01/12/2018	101	COGs 2	-5
01/12/2018	101	Labour Cost 1	-10
01/12/2018	101	Labour Costs 2	-12
01/12/2018	101	Other Costs 1	-35
01/12/2018	101	Other Costs 2	-10
01/12/2019	101	Sales	250
01/12/2019	101	Discounts	-60
01/12/2019	101	COGs 1	-35
01/12/2019	101	COGs 2	-15
01/12/2019	101	Labour Cost 1	-25
01/12/2019	101	Labour Costs 2	-22
01/12/2019	101	Other Costs 1	-50
01/12/2019	101	Other Costs 2	-3
01/12/2018	102	Sales	80
01/12/2018	102	Discounts	-5
01/12/2018	102	COGs 1	-10
01/12/2018	102	COGs 2	-5
01/12/2018	102	Labour Cost 1	-8
01/12/2018	102	Labour Costs 2	-3
01/12/2018	102	Other Costs 1	-15
01/12/2018	102	Other Costs 2	-12
01/12/2019	102	Sales	130
01/12/2019	102	Discounts	-15
01/12/2019	102	COGs 1	-15
01/12/2019	102	COGs 2	-18
01/12/2019	102	Labour Cost 1	-12
01/12/2019	102	Labour Costs 2	-14
01/12/2019	102	Other Costs 1	-20
01/12/2019	102	Other Costs 2	-18

Store	Name
101	London
102	Manchester

GL_Code	Level_1	Level_2
Sales	1_Accounting Sales	L2_Sales
Discounts	1_Accounting Sales	L2_Sales
COGs 1	2_COGs	L2_COGs
COGs 2	2_COGs	L2_COGs
Labour Cost 1	3_Labour	L2_Labour
Labour Costs 2	3_Labour	L2_Labour
Other Costs 1	4_Other Costs	L2_Other Costs
Other Costs 2	4_Other Costs	L2_Other Costs

Level_1	Sort
1_Accounting Sales	1
2_COGs	2
3_Labour	4
4_Other Costs	3

Level_2	Sort
L2_Sales	1
L2_COGs	2
L2_Labour	3
L2_Other Costs	4

@v-lionel-msft  - does the above help any? sorry so new to this forum - really appreciate your help so far - thank you!!!

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[Anonymous]

Hi @v-lionel-msft ,

Thanks so much - really helping - the only problem i have now is that in my real data i have 100 Stores and these are split in to regional groups or categories based on location...

when i use filters now on the store in your pbix the table doesnt change...

i essentially need everything still to filter other than the Level 1 rows...

Thanks,

S

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

I this this is what you want, just need create a measure to realize it. The previous solution was too complicated.

Best regards,
Lionel Chen

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