Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
i have an export of financial data (journal entries). Based on the GL account i have dfined a higher hiearchy level. I allready created a mapping table that contains all the GL accounts with a mapping to the correct hiearchy level.
reference table
data model
if i now create a table it looks like this:
1 omzet = revenue
2 KP omzet = Cost of goods sold
as you might notice there is no reference number 3, 6 and also no 9.
reference number 3 should be the gross margin and can be calculated by subtracting total of ref 2 from ref 1.
so 861.320 minus 675.386=185.934
My question comes down to this:
"how can i display reference number 3 in the same table"..
so it should look like this:
what is the best approach for this?
perhaps creation another table in which measures are stored on a row level. Using Calculate + sum in an IF statemet to calculate the total amount per reference number (1,2,4,5,7,8).
Then use the calculated values to (1,2,4,5,7,8) to calculate reference numbers 3, 6 and 9??
3 = 1 minus 2
6 = 3 minus (4+5)
9 = 6 minus (7+8)
Thanks!
Erwin
update:
calculating the the total amounts based on reference number have worked.
but when i make an active realtion between tables the calculations for reference 3,6,9 disappear
no relation
active relation
syntax of calculation
Actuals YTD =
IF('calculations'[reference number]=1;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1);
IF('calculations'[reference number]=2;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=2);
IF('calculations'[reference number]=4;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=4);
IF('calculations'[reference number]=5;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=5);
IF('calculations'[reference number]=7;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=7);
IF('calculations'[reference number]=8;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=8);
IF('calculations'[reference number]=3;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
-
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=2);
IF('calculations'[reference number]=6;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
-
CALCULATE(SUM('Journal Entries'[total amount]);
OR(OR(Mapping[reference number]=2;Mapping[reference number]=4);Mapping[reference number]=5));
IF('calculations'[reference number]=9;
CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
-
CALCULATE(SUM('Journal Entries'[total amount]);
OR(OR(OR(OR(
Mapping[reference number]=2;
Mapping[reference number]=4);
Mapping[reference number]=5);
Mapping[reference number]=7);
Mapping[reference number]=8));
0)))))))))
@amitchandak
maybe this info will be helpfull to further understand the situation
@Evandam , Make sure the reference number is present in the dimension
Then create measure like
Switch( true() ,
Max(Mapping [Reference]) =3,
calculate([measure] , Mapping [Reference] =2) -calculate([measure] , Mapping [Reference] =1)
Max(Mapping [Reference]) =6,
(calculate([measure] , Mapping [Reference] =2) -calculate([measure] , Mapping [Reference] =1) ) -
calculate([measure] , Mapping [Reference] =4) + calculate([measure] , Mapping [Reference] =5)
Max(Mapping [Reference]) =9,
<Add for 9>,
[Measure]
)
It will be a very similar approach like
Power BI Modeling- Unselected values as others - https://youtu.be/HX4pgexYwf4
Hi @amitchandak
thanks for your reply.
unfortunatly it is not very usefull in my case.
mapping table is just a table in wich all GL accounts are mapped to reference number (280 rows in total).
So i will not work to just put reference number 3,6 and 9 in this table and then calculate the total amount for these reference numbers via the measure you suggested...
thanks any way:)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |