Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |