Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey guys,
i need your help please i hava a huge table with lot of data (many excel files in one folder) and i want the differences between the Sources (excelfiles).
I have found something which is almost good for that what i want in the Quick Measures:
Solved! Go to Solution.
@KimBoehmer
If you want to follow my suggestion and disply the results in a tablevisual, then place the Sorce Name column in the table along with the following measure. From the visual format options, activate the total and rename it "Difference"
Value =
VAR MeasureValue =
SUM ( 'Zusammenfassung'[Value] )
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
)
VAR DifferenceValue =
MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] )
RETURN
IF (
HASONEVALUE ( 'Zusammenfassung'[Source.Name] ),
MeasureValue,
DifferenceValue
)
If you wish to display the difference in a seperate chart, then place the following measure in the chart ALONE (don't place any column along with it in the same chart)
Value difference =
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
)
RETURN
MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] )
Hi @KimBoehmer
Please refer to attached sample file with the solution. I've created a new column using power query to get the Source Number for sorting purposes.
Value =
VAR MeasureValue =
SUM ( 'Zusammenfassung'[Wert] )
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Date", CALCULATE ( MAX ( 'Zusammenfassung'[Source.Number] ) ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Wert] ) )
)
VAR FirstRecod =
TOPN ( 1, T1, [@Date], ASC )
VAR LastRecod =
TOPN ( 1, T1, [@Date] )
VAR DifferenceValue =
MAXX ( LastRecod, [@Value] ) - MAXX ( FirstRecod, [@Value] )
RETURN
IF (
HASONEVALUE ( 'Zusammenfassung'[Source.Name] ),
MeasureValue,
DifferenceValue
)
Hi @KimBoehmer
Please refer to attached sample file with the solution. I've created a new column using power query to get the Source Number for sorting purposes.
Value =
VAR MeasureValue =
SUM ( 'Zusammenfassung'[Wert] )
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Date", CALCULATE ( MAX ( 'Zusammenfassung'[Source.Number] ) ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Wert] ) )
)
VAR FirstRecod =
TOPN ( 1, T1, [@Date], ASC )
VAR LastRecod =
TOPN ( 1, T1, [@Date] )
VAR DifferenceValue =
MAXX ( LastRecod, [@Value] ) - MAXX ( FirstRecod, [@Value] )
RETURN
IF (
HASONEVALUE ( 'Zusammenfassung'[Source.Name] ),
MeasureValue,
DifferenceValue
)
Great thank you ver much 🙂
Hi @KimBoehmer
Please try
Value difference =
VAR __MEASURE_VALUE =
SUM ( 'Zusammenfassung'[Value] )
RETURN
SUMX (
VALUES ( 'Zusammenfassung'[Source.Name] ),
VAR __BASELINE_VALUE =
CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
RETURN
IF ( NOT ISBLANK ( __MEASURE_VALUE ), __MEASURE_VALUE - __BASELINE_VALUE )
)
Hi,
thank you for your reply.
Unfortunately this ist working.
Hi,
i get no result, there is no mistake in the formular.
and i have a slicer with the different Sources, i only want to compare two different sources at the same time.
So you want to select two sources from the slicer and the display the values of the two sources in addition to the difference between them? Is that correct? If so, I would suggest to use a table visual as it has a total where we csn disply the difference value.
Here the overview with your formular in comparison:
@KimBoehmer
If you want to follow my suggestion and disply the results in a tablevisual, then place the Sorce Name column in the table along with the following measure. From the visual format options, activate the total and rename it "Difference"
Value =
VAR MeasureValue =
SUM ( 'Zusammenfassung'[Value] )
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
)
VAR DifferenceValue =
MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] )
RETURN
IF (
HASONEVALUE ( 'Zusammenfassung'[Source.Name] ),
MeasureValue,
DifferenceValue
)
If you wish to display the difference in a seperate chart, then place the following measure in the chart ALONE (don't place any column along with it in the same chart)
Value difference =
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
)
RETURN
MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] )
Thank you very much, but i have one last question 🙂
The difference is always postiv but i have positiv an negativ chnages in the numbers how can i display them in a chart?
@KimBoehmer
Yes I made it this way as there is no logic (at least from my prespective) that defines which one minus which one. Please clarify this point.
i want to compare the volumes from 2021 to 2022 for each month and of course some month are better than last year and others not. Therefore i would need plus and minus changes
Is there a date or year-month column that defines the date of each list?
Do you to subtract the latest minus the earliest or the opposite?
Yes i have the month and yes i will substract the latest minus the erliest i have filtered.
Apologies for the late response. I was extremely busy tody I didn't have the chance to look into it.
Actually, this shouldn't be a problem we just need to filter the table or sort it by date then retrieve the amount for both max and min dates.
Value =
VAR MeasureValue =
SUM ( 'Zusammenfassung'[Value] )
VAR T1 =
ADDCOLUMNS (
VALUES ( 'Zusammenfassung'[Source.Name] ),
"@Date", CALCULATE ( MAX ( 'Zusammenfassung'[Date] ) ),
"@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
)
VAR FirstRecod =
TOPN ( 1, T1, [@Date], ASC )
VAR LastRecod =
TOPN ( 1, T1, [@Date] )
VAR DifferenceValue =
MAXX ( LastRecod, [@Value] ) - MAXX ( FirstRecod, [@Value] )
RETURN
IF (
HASONEVALUE ( 'Zusammenfassung'[Source.Name] ),
MeasureValue,
DifferenceValue
)
*UPDATE
The measure has been updated fixing one error @KimBoehmer
Hi @tamerj1 dont worry 🙂
i have tested your formular, but the result its unfortunately not correct 😞
Here two examples:
BR Kim
Please copy/paste the formula that you've used. Did you check the updated formula?
Yes, now it shows this:
For [Date] you need to use your month column. The month column has to be of YYYYMM format.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
17 | |
16 |
User | Count |
---|---|
37 | |
20 | |
19 | |
17 | |
10 |