The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |