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.
In a Matrix visual the column shows
Sales,
MoM Diff ( Current month-Previous Month)
Neg Diff (Negative values in MoM Diff)
Main objective is to obtain the total for "Neg Diff" (columns total).
Sales, MoM Diff and Neg Diff are measures.
Required output shown below
Thanks in advance.
Cheers!
The data table is shown below,
The objective is to calculate the month over month sales difference and take sum of total negative sales difference for each month.
Required output is shown below,
I wasnot able to obtain this on Power BI, what I got is shown below, the total for MoM change and MoM -ve is the same.
Formulas used,
I tired another formula with MoM -ve,
Please help with a solution.
The main objective is to find total for negative month difference in sales.
Thanks in advance.
@Anonymous Hey ,
Use below formula
I think it will help to remove the date context when creating the list of customers.
MoM -ve_3 =
VAR Customers =
CALCULATETABLE ( VALUES ( Sales[Customer] ), ALLSELECTED ( 'Calendar' ) )
RETURN
SUMX ( Customers, [MoM Change] )
It didnt work, output shown below.
I think I accidentally put the wrong measure inside the SUMX. Does it work with [Mom -ve] instead of [MoM Change]?
Hi,
Thank you.
Its working fine when all months are selected. When only one month is selected it dsnt work. And even when two consecutive months are selected it works.
Can you find a solution for that too?
Try ALL instead of ALLSELECTED.
// Something like this...
// Adjust if needed.
[Neg MoM] =
var negSum =
SUMX(
Customer, // the Customer dimension
var diff = [MoM Diff]
return
( diff < 0 ) * diff
)
return
if( negSum, negSum )
It didnt work.
@Anonymous
I can't tell you more since you don't give me more information and I don't know your model. My guess was the best I could do. In my model it does work.
I have explained it detail in
https://community.powerbi.com/t5/Desktop/Sum-of-Negative-values/m-p/1787470
Please do check.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |