topic Re: Divide all categories by one category-total in DAX Commands and Tips

Divide all categories by one category-total

RCL — Thu, 03 Nov 2022 17:06:26 GMT

Hello all,

If anyone can help me it is much appreciated.

I have a financial report were I divide the differend revenues by the total revenue. This is working.
But now I like to divide the costs also by the total revenue. I first made a formula for the total Revenue.

Kosten%4 =

CALCULATE(SUM(GeneralLedgerBalances[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr]))

(I added 'Rapportage_Grootboek'[reknr]), because 'Rapportage_Grootboek'[oms25_0] is sorted by Rapportage_Grootboek'[reknr]))

After this I first tested if I got the right percentages of the revenue, and this was working.

Kosten%1 =

VAR Omzetverpakken =

CALCULATE(SUM(GeneralLedgerBalances[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr]))

RETURN

DIVIDE(

CALCULATE(SUM(GeneralLedgerBalances[AmountDC]),

ALL(Volgorde[Rapportage omschrijving]),

('Volgorde'[Rapportage omschrijving]) = "Omzet verpakken")

Omzetverpakken

,0)

Now I thought it was simple to get all the percentages by replacing the "Numerator" in:

Kosten%2 =

VAR Omzetverpakken =

CALCULATE(SUM('GeneralLedgerBalances'[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr]))

RETURN

Divide(

SUM('GeneralLedgerBalances'[AmountDC]),

Omzetverpakken,

For the revenue it is still working, but for the other categories not. I get only zero's because of the alternate result.
So, I deleted the Divide functions and made the following formula:

Kosten%7 =

VAR Omzetverpakken =

CALCULATE(SUM('GeneralLedgerBalances'[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr]),

VALUES(Volgorde[Rapportage omschrijving])

)

RETURN

SUM('GeneralLedgerBalances'[AmountDC])

Omzetverpakken

For the revenue this is still working, but now I get a message for the other categories. See the results below.

By the way, the percentage of the Total 0,23 is shown and is correct.

I have search a lot of time to solve this problem, but I overlook something. And I hoop that one of you can help me with this.

Thank you in advance!

Re: Divide all categories by one category-total

Anonymous — Fri, 04 Nov 2022 09:43:24 GMT

Hi RCL ,

You can try to create a measure.

Kosten%2 = VAR Omzetverpakken = CALCULATE ( SUM ( 'GeneralLedgerBalances'[AmountDC] ), 'Volgorde'[Rapportage omschrijving] = "Omzet verpakken", ALLSELECTED ( 'Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr] ) ) RETURN DIVIDE ( SUMX ( SUMMARIZE ( 'Rapportage_Grootboek', 'Rapportage_Grootboek'[column1], 'Rapportage_Grootboek'[column2], "measure", SUM ( 'GeneralLedgerBalances'[AmountDC] ) ), [measure] ), Omzetverpakken, 0 )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: Divide all categories by one category-total

RCL — Sat, 05 Nov 2022 09:31:53 GMT

Hello Anonymous ,

Thanks for your reply!

I have tried your formula. I added one table. See below.
Unfortunately I get the same result.

Kosten%8 =

VAR Omzetverpakken =

CALCULATE (

SUM ( 'GeneralLedgerBalances'[AmountDC] ),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED ( 'Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr] )

)

RETURN

DIVIDE (

SUMX (

SUMMARIZE (

'Rapportage_Grootboek',

'Volgorde subtotals'[Subtotals],

'Rapportage_Grootboek'[oms25_0],

'Rapportage_Grootboek'[reknr],

"measure", SUM ( 'GeneralLedgerBalances'[AmountDC] )

[measure]

Omzetverpakken,

)

First I will show you what the end result must be:

So all the amounts and subtotals must be divided by EURO 385.177, the subtotal of 'Omzet verpakken'.

I use the following tables:

'GeneralledgerBalances'[AmountDC] and 'GeneralledgerBalances'[Date]

2) 'Rapportage_Grootboek'[oms25_0] and 'Rapportage_Grootboek'[reknrt]. Last one to sort the first one.

3) 'Volgorde'[Rapportage omschrijving]

4) 'Volgorde Subtotals'[Subtotals]

Between those I have a relation between 'Rapporage omschrijving'.

And I use the following columns in my Matrix:

"Omzetvergelijk" = 'Volgorde subtotals'[Subtotals]

I hope it is clear now what I like to have in my report. Not a very strange report I think, but somewhere I am making a mistake? I hope you or someone else can help me further.

If you do not have enough information I will make a simplefied pbix file. Please let me know if that is necessary.

Thank you again!

Best regards,

Robert Links

Re: Divide all categories by one category-total

RCL — Thu, 17 Nov 2022 17:29:31 GMT

I found the solution myself. Maybe someone else has the same problem and can use this solution.

First measure is for % of subtotals when the revenue is 100%.

1.0.3_Totale_Omzet_Verpakken =

CALCULATE(

SUM('GeneralLedgerBalances'[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Volgorde'[Rapportage omschrijving]),

ALLSELECTED('Volgorde'[Volgorde])

Second measure is for the rows divided by 100% revenue.

1.0.4_Totale_Omzet_Verpakken =

CALCULATE(

SUM('GeneralLedgerBalances'[AmountDC]),

'Volgorde'[Rapportage omschrijving] = "Omzet verpakken",

ALLSELECTED('Volgorde'[Rapportage omschrijving]),

ALLSELECTED('Volgorde'[Volgorde]),

ALLSELECTED(Rapportage_Grootboek))

Last measure gives the right percentages per row and per subtotal:

VAR Totale_BR_per_Rapportomschrijving = ([1.0.3_Totale_Omzet_Verpakken])

VAR Totale_BR_per_RapportageGrootboek = ([1.0.4_Totale_Omzet_Verpakken])

VAR Pct_of_Rapportomchrijving =

Divide( Totale_Bruto_Resultaat,Totale_BR_per_Rapportomschrijving,0)

VAR Pct_of_Rapportagegrootboek =

Divide( Totale_Bruto_Resultaat,Totale_BR_per_RapportageGrootboek,0)

VAR Resultaat =

CALCULATE(

SWITCH( TRUE(),

ISINSCOPE('Rapportage_Grootboek'[oms25_0]), Pct_of_Rapportagegrootboek,

ISINSCOPE('Volgorde'[Rapportage omschrijving]), Pct_of_Rapportomchrijving,

Pct_of_Rapportomchrijving))

Return

Resultaat