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 created a calculated table as follows:
Solved! Go to Solution.
Not sure what are you trying to achieve, but you may try
CurrentPct =
VAR CurrentYear =
MAX ( DistributionPerCompany[Year] )
VAR CurrentCompany =
MAX ( DistributionPerCompany[Company] )
VAR CompanyMSS =
SUMMARIZE (
FILTER (
ALLSELECTED ( DistributionPerCompany ),
DistributionPerCompany[ProdType] = "Distribution"
&& DistributionPerCompany[Year] = CurrentYear
),
DistributionPerCompany[Year],
DistributionPerCompany[Company],
"Qty", SUM ( DistributionPerCompany[RealQty] )
)
VAR TotalQty =
SUMX ( CompanyMSS, [Qty] )
VAR Qty =
SUMX (
FILTER ( CompanyMSS, DistributionPerCompany[Company] = CurrentCompany ),
[Qty]
)
RETURN
100 * Qty / TotalQty
Hi @SajjadMousavi
Almost the same
CurrentPct =
VAR TotalQty =
CALCULATE (
SUM ( CompanyMSSummarized[Qty] ),
FILTER (
ALL ( CompanyMSSummarized ),
CompanyMSSummarized[Year] = EARLIER ( CompanyMSSummarized[Year] )
)
)
RETURN
100 * SUM ( CompanyMSSummarized[Qty] ) / TotalQty
PrevPct =
CALCULATE (
SUM ( CompanyMSSummarized[CurrentPct] ),
FILTER (
ALL ( CompanyMSSummarized ),
CompanyMSSummarized[Company] = EARLIER ( CompanyMSSummarized[Company] )
&& CompanyMSSummarized[Year]
= EARLIER ( CompanyMSSummarized[Year] ) - 1
)
)
Thansk for your reply, but I'm looking for a way to skip creation of summarized table. In addition, you cannot use EARLIER function in a measure, as measres are not aware of row context.
@SajjadMousavi
There is a row context created by FILTER. However, please clarify exactly what is required. I'm a little confused. Thank you
I just want to skip creation of CompanyMSSummarized table in data model (want to create it on-the-fly in some way) and do calculations. But when I summarize DistributionPerCompany in a measure, I cannot use it in subsequent calculations (like the one you suggested) and Power BI gives this error:
1. You absolutely can create a table on the fly, store it in a var and USE it subsequently. The only thing you're missing in your code is the correct references to the columns of the table. You cannot use the name of the variable to refer to them. For instance, you have to use SUMX and the original names of the columns. It's all about DATA LINEAGE.
2. You should never use SUMMARIZE to perform calculations in its body since SUMMARIZE is a very buggy (and complex) function (very dangerous in measures), and MS have refused fixing it a long time ago. Instead, you should use the hybrid SUMMARIZE/ADDCOLUMNS. Use SUMMARIZE only to get the distinct combination of rows from a (extended) table.
3. You should also never filter a full table (row 7 in your code). Always only the minimum number of columns that are needed.
4. EARLIER will not work in your code because to use it you have to have 2 different row contexts active at the same time. One is not enough. Incidentally, you should never use this function. Instead, you should use variables.
Best
Thanks a lot for details. About 3rd point you mentioned, this table has exactly minimum number of columns needed for calculations. Summarization is just to sum up data per year (as can be seend from code). Anyway, I will try to re-write the code using guidelines you gave. Hope I can do it.
Thanks in advance for your time:
CurrentPct =
VAR CompanyMSS = SUMMARIZE(FILTER(DistributionPerCompany, DistributionPerCompany[ProdType] = "Distribution"), DistributionPerCompany[Year], DistributionPerCompany[Company], "Qty", SUM(DistributionPerCompany[RealQty]))
VAR TotalQty =
CALCULATE (
SUM ( CompanyMSS[Qty] ),
FILTER (
ALL ( CompanyMSS ),
CompanyMSS[Year] = EARLIER ( CompanyMSS[Year] )
)
)
RETURN
100 * SUM ( CompanyMSS[Qty] ) / TotalQty
Not sure what are you trying to achieve, but you may try
CurrentPct =
VAR CurrentYear =
MAX ( DistributionPerCompany[Year] )
VAR CurrentCompany =
MAX ( DistributionPerCompany[Company] )
VAR CompanyMSS =
SUMMARIZE (
FILTER (
ALLSELECTED ( DistributionPerCompany ),
DistributionPerCompany[ProdType] = "Distribution"
&& DistributionPerCompany[Year] = CurrentYear
),
DistributionPerCompany[Year],
DistributionPerCompany[Company],
"Qty", SUM ( DistributionPerCompany[RealQty] )
)
VAR TotalQty =
SUMX ( CompanyMSS, [Qty] )
VAR Qty =
SUMX (
FILTER ( CompanyMSS, DistributionPerCompany[Company] = CurrentCompany ),
[Qty]
)
RETURN
100 * Qty / TotalQty
Thanks a lot. I was so busy that I couldn't check here. Will test your solution.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |