Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a fact tables for Orders (POrders) and for Invoices (PInvoices) which are sliced by dimension tables, one for Suppliers and one for PriceGroups.
This is an extraction of my datamodel.
I would like to analyze the data by Pricegroups in a matrix, and basing on a given parameter [_CostEvalMode] (which is stored in a dedicated Parameter table) using the amount resulting from the Invoice measure which is called [PurmMod770PInvEUR] or use whatever is greater Order amount [OrderValueEUR] or invoice amount [PurmMod770PInvEUR] .
This is my measure.
CostCommittedPG =
VAR _T1 =
ADDCOLUMNS (
SUMMARIZE ( Suppliers, Suppliers[NAME] ),
"@OrderValueEUR", [OrderValueEUR],
"@InvoiceValueEUR", [PurmMod770PInvEUR]
)
VAR _Result =
IF (
HASONEVALUE ( PriceGroups[PRICEGROUP] ),
SWITCH (
[_CostEvalMode],
"PO/INV - MAX",
IF (
[OrderValueEUR] > [PurmMod770PInvEUR],
[OrderValueEUR],
[PurmMod770PInvEUR]
),
"INV", [PurmMod770PInvEUR]
),
SUMX (
_T1,
SWITCH (
[_CostEvalMode],
"PO/INV - MAX",
IF (
[@OrderValueEUR] > [@InvoiceValueEUR],
[@OrderValueEUR],
[@InvoiceValueEUR]
),
"INV", [@InvoiceValueEUR]
)
)
)
RETURN
_Result
The POrders table as well as PInvoices table contain data with no Suppliers and no Pricegroups, while no Pricegroup is much more often the case as no Supplier.
These are the resulting matrixes, left side sliced by Pricegroup and right side sliced by Supplier.
In this scenario the Parameter for [_CostEvalMode] is "INV " consequently it only considers the invoiced amount.
In the [CostscommittedPG] measure I'm summarizing and computing by Suppliers at the grand total level. This because Orders and Invoices are most of the times linked through a suppliers.
The SUMMARIZE operation apparantely ignores data with no supplier consequently the resutl of the [CostscommittedPG] at the grand total level is 17.365€ less than the total invoiced amount which is 1.737.106€.
How can I include the 17.365€ which have no supplier at the grand total level in the [CostscommittedPG] measure?
Thanks in advance.
Br
Solved! Go to Solution.
Try changing the SUMMARIZE to VALUES(Suppliers[NAME]), that may add the blank row which is needed
Try changing the SUMMARIZE to VALUES(Suppliers[NAME]), that may add the blank row which is needed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |