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.
Hi Community,
I am trying to sum all the "1" in a measure, which should then be visualized in a tile. If I set the column into my datatable it shows the "1" in the right columns, however the total in the bottum is 0. I think my measure somehow is turning it into a true/false statement, which might could be the problem. I have added my measure and a table below in order to indicate what I am trying to achieve.
Styles Not Bought =
if(HASONEFILTER(Style_Status[Style_Key]);
CALCULATE(
DISTINCTCOUNT(Fact_Open_Sales_Colour[Style Key]);
FILTER(Style_Status;[Incoming Order2]=0);
Filter(Fact_Open_Sales_Colour;[Sold EUR C]>=0);
FILTER(Fact_Open_Sales_Colour;DISTINCTCOUNT(Fact_Open_Sales_Colour[Holding Name])=
DISTINCTCOUNT(Dim_Customers_Detail[HOLDING_NAME])));0)
Stylekey | Holding Name | Sold | Styles Not Bought |
123456789 | Company A | 100 | 1 |
234567891 | Company B | 90 | 0 |
345678912 | Company C | 80 | 0 |
456789123 | Company D | 70 | 1 |
567891234 | Company E | 60 | 0 |
Total | 0 |
The Tile/Measure should then count the two "1" and show a total of 2.
Solved! Go to Solution.
Create a new measure with sumx(Facttable;[Measure with IF-Statement]). This measure should distinguish each row from 0 and 1 and aggregate correctly in the total.
Does it work?
Cheers
Hi,
According to your description, i create a table to test:
Then, create a measure:
Measure = SUMX(DISTINCT('Table'[ID]),IF(CALCULATE(SUM('Table'[Sales]))>110,1,0))
And it shows the correct total:
Hope this helps.
Best Regards,
Giotto Zhi
Hello @Anonymous,
did you try to wrap the whole thing into a sum? The outer function is an IF-statement.. so PBI doesn't know how to aggregate.
Cheers Xriz
Just to be sure that I understand what you are writing.
You want me to put "Sum()" around the whole thing? If that is the case, then it gives me an error saying the 'sum function only acceots a column reference as an argument'.
Create a new measure with sumx(Facttable;[Measure with IF-Statement]). This measure should distinguish each row from 0 and 1 and aggregate correctly in the total.
Does it work?
Cheers
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |