Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Experts,
Can some one helpto resolve this, I have a table with few colums, main are Country , Products, Year, Month, Sales QTY, Value. and two table filters, one to select the year table filter named Filter[Year], another one to select the Type either Qty or value, name Filter[Type]
what is required is when user selects,
select QTY, and year 2020 & 2019 then sum of qty2020/qty2019
select QTY, and year 2021 & 2020 then sum of qty 2021/qty2020
select QTY, and year 2021 & 2019 then sum of qty 2021/qty2019
if all three years selected, then 0 or any message
similary id Value is selected
Select Value2, and year 2020 & 2019 then sum of value2020/value 2019
Select value2, and year 2021 & 2020 then sum of value2021/value 2020
Select value2, and year 2021 & 2019 then sum of value 2021/value 2019
if all three years selected, then 0 or any message.
can some experts please help me
Solved! Go to Solution.
Hi @MSH ,
According to my understand , you want to calculate the QTY/Value of the larger year divide the previous year, right?
You could use the following formula after unpivot the QTY and Value columns:
Measure =
VAR _seleYear =
ALLSELECTED ( 'Table'[Year] )
VAR _QTYOrValue =
SWITCH (
SELECTEDVALUE ( 'Table'[Attribute] ),
"QTY", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "QTY" ),
"Value", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "Value" )
)
VAR _seleAttribute =
SELECTEDVALUE ( 'Table'[Attribute] )
VAR _countSelectedItems =
CALCULATE ( COUNTROWS ( FILTERS ( 'Table'[Year] ) ), _seleYear )
VAR _thelargerYear =
CALCULATE (
MAX ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Year] IN _seleYear )
)
VAR _thePreviousYear =
CALCULATE (
MIN ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Year] IN _seleYear )
)
RETURN
IF (
_countSelectedItems = 2,
CALCULATE (
SUM ( 'Table'[Value.1] ),
FILTER (
'Table',
'Table'[Attribute] = _seleAttribute
&& 'Table'[Year] = _thelargerYear
)
)
/ CALCULATE (
SUM ( 'Table'[Value.1] ),
FILTER (
'Table',
'Table'[Attribute] = _seleAttribute
&& 'Table'[Year] = _thePreviousYear
)
),
0
)
My visualization looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @MSH ,
According to my understand , you want to calculate the QTY/Value of the larger year divide the previous year, right?
You could use the following formula after unpivot the QTY and Value columns:
Measure =
VAR _seleYear =
ALLSELECTED ( 'Table'[Year] )
VAR _QTYOrValue =
SWITCH (
SELECTEDVALUE ( 'Table'[Attribute] ),
"QTY", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "QTY" ),
"Value", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "Value" )
)
VAR _seleAttribute =
SELECTEDVALUE ( 'Table'[Attribute] )
VAR _countSelectedItems =
CALCULATE ( COUNTROWS ( FILTERS ( 'Table'[Year] ) ), _seleYear )
VAR _thelargerYear =
CALCULATE (
MAX ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Year] IN _seleYear )
)
VAR _thePreviousYear =
CALCULATE (
MIN ( 'Table'[Year] ),
FILTER ( 'Table', 'Table'[Year] IN _seleYear )
)
RETURN
IF (
_countSelectedItems = 2,
CALCULATE (
SUM ( 'Table'[Value.1] ),
FILTER (
'Table',
'Table'[Attribute] = _seleAttribute
&& 'Table'[Year] = _thelargerYear
)
)
/ CALCULATE (
SUM ( 'Table'[Value.1] ),
FILTER (
'Table',
'Table'[Attribute] = _seleAttribute
&& 'Table'[Year] = _thePreviousYear
)
),
0
)
My visualization looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Thank you so much,
it worked
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |