The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I have a table that looks like below.
Date Value DataType
2022/01/01 10 A
2022/01/02 15 A
2022/01/03 10 A
2022/02/01 11 A
2022/02/02 23 A
2022/02/03 10 A
2022/01/01 22 B
2022/01/02 55 B
2022/01/03 12 B
2022/02/01 73 B
2022/02/02 23 B
2022/02/03 14 B
2022/03/01 15 B
2022/03/02 23 B
2022/03/03 22 B
I need to return a MONTHLY SUM of the values, where if data type A exists, we use that number, however if A does not exist for the month, we use the total for data type B.
Expected results of the example above are:
MONTH Value
Jan-22 35
Feb-22 44
Jan-22 60
As you can see, there is data for type A for the first 2 months, but for the 3rd, there is only type B, so we use type B.
I have created 2 measures, 1 to retrieve the data for type A (MeasureA) and one to retrieve type B (MeasureB).
For the purposes of my report, these are using filters to each get their respective datatype.
I have tried the following:
MeasureC =
MONTH Value
Jan-22 35
Feb-22 44
Jan-22 60
TOTAL 79
Does anyone have any suggestions on another way to correctly do this?
@amitchandak you've been able to answer almost all of my questions. Hopefully you can help with this one! 🙂
Solved! Go to Solution.
HI @MCassady ,
Please try the following:
Base data
in my example I assum you have a date table connected to your table
First the version with right rows and wrong total,. So if you do not need a total you can take this one, but then also turn off the total
AB_SUM =
var var_SumA =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "A"
)
var var_SumB =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "B"
)
RETURN
if(
NOT ISBLANK(var_SumA)
,var_SumA
,var_SumB
)
If you also need the total then please take this formula
AB_SUM =
SUMX(
VALUES(DimDate[Year-Month]),
var var_SumA =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "A"
)
var var_SumB =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "B"
)
RETURN
if(
NOT ISBLANK(var_SumA)
,var_SumA
,var_SumB
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
@MCassady Thanks for sharing the problem.
I have one question on your sample data. What would be logic if data for both A and B datatype is available for the same month, like in the below snapshot.
If both are available, we prioritize type A and ignore that B exists.
In sql it would be like having an ISNULL(A,B)
HI @MCassady ,
Please try the following:
Base data
in my example I assum you have a date table connected to your table
First the version with right rows and wrong total,. So if you do not need a total you can take this one, but then also turn off the total
AB_SUM =
var var_SumA =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "A"
)
var var_SumB =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "B"
)
RETURN
if(
NOT ISBLANK(var_SumA)
,var_SumA
,var_SumB
)
If you also need the total then please take this formula
AB_SUM =
SUMX(
VALUES(DimDate[Year-Month]),
var var_SumA =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "A"
)
var var_SumB =
CALCULATE(
SUM(SampleAB[Value]),
SampleAB[DataType] = "B"
)
RETURN
if(
NOT ISBLANK(var_SumA)
,var_SumA
,var_SumB
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
@Mikelytics @amitchandak @Bifinity_75
This seems to be the solution, I had the sumx in the wrong spot and double checked this post.
Thank you for the swift responses, you guys are the best!
Edit: Actually, it may be a long shot. But is there a way to do some conditional formatting to highlight which monthly values are from each datatype on the table? I have a feeling there isn't....
Example:
@MCassady , Please first refer to the reply by @Bifinity_75 , if that does not work try
sumx(Values('Date'[Month Year]), calculate(IF (ISBLANK([MeasureA]),[MeasureB],[MeasureA])) )
Hi @MCassady , try this measure:
Value = var A_= CALCULATE(sum('Table'[ Value ]),'Table'[DataType]="A")
var B_=CALCULATE(sum('Table'[ Value ]),'Table'[DataType]="B")
return
if (A_=BLANK(),B_,A_)
Best regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
135 | |
101 | |
89 | |
72 | |
58 |
User | Count |
---|---|
262 | |
120 | |
115 | |
95 | |
82 |